Yang Perlu Diketahui
- Pertama, buat fungsi INDEX, lalu mulai fungsi MATCH bersarang dengan memasukkan argumen Lookup_value.
- Selanjutnya, tambahkan argumen Lookup_array diikuti dengan argumen Match_type, lalu tentukan rentang kolom.
- Kemudian, ubah fungsi bersarang menjadi rumus array dengan menekan Ctrl+ Shift+ Enter. Terakhir, tambahkan istilah pencarian ke lembar kerja.
Artikel ini menjelaskan cara membuat rumus pencarian yang menggunakan beberapa kriteria di Excel untuk menemukan informasi dalam database atau tabel data dengan menggunakan rumus array. Rumus array melibatkan pengumpulan fungsi MATCH di dalam fungsi INDEX. Informasi mencakup Excel untuk Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, dan Excel untuk Mac.
Ikuti Tutorialnya
Untuk mengikuti langkah-langkah dalam tutorial ini, masukkan data sampel ke dalam sel berikut, seperti yang ditunjukkan pada gambar di bawah ini. Baris 3 dan 4 dibiarkan kosong untuk mengakomodasi rumus array yang dibuat selama tutorial ini. (Perhatikan bahwa tutorial ini tidak menyertakan format yang terlihat pada gambar.)
- Masukkan rentang data teratas ke dalam sel D1 hingga F2.
- Masukkan rentang kedua ke dalam sel D5 hingga F11.
Membuat Fungsi INDEX di Excel
Fungsi INDEX adalah salah satu dari sedikit fungsi di Excel yang memiliki banyak bentuk. Fungsi ini memiliki Formulir Array dan Formulir Referensi. Formulir Array mengembalikan data dari database atau tabel data. Formulir Referensi memberikan referensi sel atau lokasi data dalam tabel.
Dalam tutorial ini, Formulir Array digunakan untuk menemukan nama pemasok untuk widget titanium, daripada referensi sel ke pemasok ini di database.
Ikuti langkah-langkah ini untuk membuat fungsi INDEX:
- Pilih sel F3 untuk menjadikannya sel aktif. Sel ini adalah tempat fungsi bersarang akan dimasukkan.
-
Pergi ke Rumus.
Image - Pilih Cari & Referensi untuk membuka daftar drop-down fungsi.
- Pilih INDEX untuk membuka kotak dialog Pilih Argumen.
- Pilih array, row_num, column_num.
- Pilih OK untuk membuka kotak dialog Function Arguments. Di Excel untuk Mac, Pembuat Rumus akan terbuka.
- Tempatkan kursor di kotak teks Array.
-
Sorot sel D6 melalui F11 di lembar kerja untuk memasukkan rentang ke dalam kotak dialog.
Biarkan kotak dialog Argumen Fungsi terbuka. Formulanya belum selesai. Anda akan menyelesaikan rumus dalam petunjuk di bawah ini.
Image
Mulai Fungsi MATCH Bersarang
Saat menyarangkan satu fungsi di dalam fungsi lain, tidak mungkin membuka pembuat rumus fungsi kedua, atau bersarang, untuk memasukkan argumen yang diperlukan. Fungsi bersarang harus dimasukkan sebagai salah satu argumen dari fungsi pertama.
Saat memasukkan fungsi secara manual, argumen fungsi dipisahkan satu sama lain dengan koma.
Langkah pertama untuk memasukkan fungsi MATCH bersarang adalah memasukkan argumen Lookup_value. Lookup_value adalah lokasi atau referensi sel untuk istilah pencarian yang akan dicocokkan dalam database.
The Lookup_value hanya menerima satu kriteria atau istilah pencarian. Untuk mencari beberapa kriteria, perluas Lookup_value dengan menggabungkan, atau menggabungkan, dua atau lebih referensi sel menggunakan simbol ampersand (&).
-
Dalam kotak dialog Function Arguments, letakkan kursor di kotak teks Row_num.
- Masukkan MATCH(.
- Pilih sel D3 untuk memasukkan referensi sel tersebut ke dalam kotak dialog.
- Masukkan & (ampersand) setelah referensi sel D3 untuk menambahkan referensi sel kedua.
- Pilih sel E3 untuk memasukkan referensi sel kedua.
-
Enter , (koma) setelah referensi sel E3 untuk menyelesaikan entri argumen Lookup_value fungsi MATCH.
Image Pada langkah terakhir tutorial, Nilai_Pencarian akan dimasukkan ke dalam sel D3 dan E3 lembar kerja.
Lengkapi Fungsi MATCH Bersarang
Langkah ini mencakup penambahan argumen Lookup_array untuk fungsi MATCH bersarang. Lookup_array adalah rentang sel yang dicari oleh fungsi MATCH untuk menemukan argumen Lookup_value yang ditambahkan pada langkah tutorial sebelumnya.
Karena dua bidang pencarian diidentifikasi dalam argumen Lookup_array, hal yang sama harus dilakukan untuk Lookup_array. Fungsi MATCH hanya mencari satu larik untuk setiap istilah yang ditentukan. Untuk memasukkan beberapa larik, gunakan ampersand untuk menggabungkan larik.
- Letakkan kursor di akhir data dalam kotak teks Row_num. Kursor muncul setelah koma di akhir entri saat ini.
- Sorot sel D6 melalui D11 di lembar kerja untuk memasukkan rentang. Rentang ini adalah larik pertama yang dicari oleh fungsi.
- Masukkan & (sebuah ampersand) setelah referensi sel D6:D11. Simbol ini menyebabkan fungsi mencari dua array.
- Sorot sel E6 melalui E11 di lembar kerja untuk memasukkan rentang. Rentang ini adalah larik kedua yang dicari oleh fungsi.
-
Masukkan , (koma) setelah referensi sel E3 untuk melengkapi entri argumen Lookup_array fungsi MATCH.
Image - Biarkan kotak dialog terbuka untuk langkah berikutnya dalam tutorial.
Tambahkan Argumen Tipe MATCH
Argumen ketiga dan terakhir dari fungsi MATCH adalah argumen Match_type. Argumen ini memberi tahu Excel cara mencocokkan Lookup_value dengan nilai di Lookup_array. Pilihan yang tersedia adalah 1, 0, atau -1.
Argumen ini opsional. Jika dihilangkan, fungsi menggunakan nilai default 1.
- Jika Match_type=1 atau dihilangkan, MATCH menemukan nilai terbesar yang lebih kecil atau sama dengan Lookup_value. Data Lookup_array harus diurutkan dalam urutan menaik.
- Jika Match_type=0, MATCH menemukan nilai pertama yang sama dengan Lookup_value. Data Lookup_array dapat diurutkan dalam urutan apa pun.
- Jika Match_type=-1, MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan Lookup_value. Data Lookup_array harus diurutkan dalam urutan menurun.
Masukkan langkah-langkah ini setelah koma dimasukkan pada langkah sebelumnya pada baris Row_num dalam fungsi INDEX:
- Masukkan 0 (nol) setelah koma di kotak teks Row_num. Angka ini menyebabkan fungsi bersarang mengembalikan kecocokan persis dengan istilah yang dimasukkan dalam sel D3 dan E3.
-
Masukkan ) (tanda kurung tutup) untuk menyelesaikan fungsi MATCH.
Image - Biarkan kotak dialog terbuka untuk langkah berikutnya dalam tutorial.
Selesaikan Fungsi INDEX
Fungsi MATCH selesai. Saatnya pindah ke kotak teks Column_num dari kotak dialog dan masukkan argumen terakhir untuk fungsi INDEX. Argumen ini memberi tahu Excel bahwa nomor kolom berada dalam kisaran D6 hingga F11. Rentang ini adalah tempat ia menemukan informasi yang dikembalikan oleh fungsi. Dalam hal ini, pemasok untuk widget titanium.
- Tempatkan kursor di kotak teks Column_num.
-
Masukkan 3 (nomor tiga). Angka ini memberitahu rumus untuk mencari data di kolom ketiga dari range D6 sampai F11.
Image - Biarkan kotak dialog terbuka untuk langkah berikutnya dalam tutorial.
Buat Rumus Array
Sebelum menutup kotak dialog, ubah fungsi bersarang menjadi rumus array. Array ini memungkinkan fungsi untuk mencari beberapa istilah dalam tabel data. Dalam tutorial ini, dua istilah dicocokkan: Widget dari kolom 1 dan Titanium dari kolom 2.
Untuk membuat rumus array di Excel, tekan CTRL, SHIFT, dan ENTERtombol secara bersamaan. Setelah ditekan, fungsi tersebut dikelilingi oleh kurung kurawal, yang menunjukkan bahwa fungsi tersebut sekarang adalah array.
- Pilih OK untuk menutup kotak dialog. Di Excel untuk Mac, pilih Done.
- Pilih sel F3 untuk melihat rumus, lalu letakkan kursor di akhir rumus di Formula Bar.
- Untuk mengonversi rumus menjadi array, tekan CTRL+ SHIFT+ ENTER.
- A N/A kesalahan muncul di sel F3. Ini adalah sel tempat fungsi dimasukkan.
-
Kesalahan N/A muncul di sel F3 karena sel D3 dan E3 kosong. D3 dan E3 adalah sel tempat fungsi terlihat untuk menemukan Nilai_Pencarian. Setelah data ditambahkan ke dua sel ini, kesalahan diganti dengan informasi dari database.
Image
Tambahkan Kriteria Pencarian
Langkah terakhir adalah menambahkan istilah pencarian ke lembar kerja. Langkah ini cocok dengan istilah Widget dari kolom 1 dan Titanium dari kolom 2.
Jika rumus menemukan kecocokan untuk kedua istilah di kolom yang sesuai dalam database, formula akan mengembalikan nilai dari kolom ketiga.
- Pilih sel D3.
- Masukkan Widget.
- Pilih sel E3.
- Ketik Titanium, dan tekan Enter.
- Nama pemasok, Widgets Inc., muncul di sel F3. Ini adalah satu-satunya pemasok terdaftar yang menjual Titanium Widget.
-
Pilih sel F3. Fungsi muncul di bilah rumus di atas lembar kerja.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
Dalam contoh ini, hanya ada satu pemasok untuk widget titanium. Jika ada lebih dari satu pemasok, pemasok yang terdaftar pertama di database dikembalikan oleh fungsi.
Image
