Pencarian Multi-Kriteria dengan INDEX-MATCH

Terkadang satu kolom tidak dapat mengidentifikasi suatu baris secara unik. Anda mungkin memerlukan harga produk dalam ukuran tertentu, atau gaji karyawan di departemen tertentu.

Untuk itu diperlukan pencarian dengan beberapa kriteria: mencocokkan dua kolom atau lebih sekaligus untuk menentukan tepat satu baris.

INDEX-MATCH menangani hal ini dengan elegan dengan menggabungkan kondisi menjadi satu pengujian pencocokan, tanpa memerlukan kolom bantuan tambahan.

Model mental paling sederhana adalah menggabungkan kolom-kolom kunci Anda menjadi satu. Tambahkan kolom bantuan yang menggabungkan produk dan ukuran, lalu lakukan pencarian biasa pada kolom tersebut.

Misalnya, sebuah sel bantuan dapat berisi =A2&"|"&B2, yang menghasilkan "Shirt|Large". Selanjutnya, Anda mencocokkan "Shirt|Large" dengan kolom gabungan tersebut.

Cara ini berfungsi, tetapi membuat lembar Anda menjadi penuh. Bagian berikutnya menunjukkan cara melewati kolom bantuan sepenuhnya.

Trik utamanya: kalikan kedua pengujian kondisi di dalam MATCH.

(A2:A10=G1) menghasilkan array TRUE/FALSE untuk kriteria pertama. (B2:B10=G2) melakukan hal yang sama untuk kriteria kedua. Dengan mengalikannya, (A2:A10=G1)*(B2:B10=G2), hasilnya adalah 1 hanya ketika keduanya TRUE dan 0 di tempat lainnya.

Selanjutnya, MATCH mencari nilai 1 untuk menemukan baris yang memenuhi kedua kondisi.

Dalam lembar sebar, TRUE berperilaku sebagai 1 dan FALSE sebagai 0. Mengalikan keduanya meniru AND logis:

• 1 dikali 1 = 1 (kedua kondisi terpenuhi)
• 1 dikali 0 = 0
• 0 dikali 1 = 0
• 0 dikali 0 = 0

Jadi, hanya baris yang memenuhi kedua kriteria yang menghasilkan angka 1. Setiap baris lainnya menjadi 0. Angka 1 itulah yang menandai baris yang kita inginkan.

Sekarang bungkus array yang telah dikalikan tersebut dalam MATCH, lalu cari nilai tepat 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) mengembalikan posisi baris pertama yang kedua kondisinya TRUE.

Jika kombinasi yang cocok berada di baris data keempat, MATCH mengembalikan 4. Posisi itulah yang diperlukan INDEX untuk mengambil jawabannya.

Masukkan hasil MATCH tersebut ke INDEX pada kolom yang benar-benar Anda inginkan, misalnya harga di C2:C10.

Rumus lengkapnya berarti: dari C2:C10, kembalikan nilai pada baris ketika produk sama dengan G1 dan ukuran sama dengan G2.

Ini adalah pencarian dengan beberapa kriteria yang sebenarnya, tanpa kolom bantuan dan tanpa mengubah susunan data Anda.

Rumus ini mengevaluasi array kondisi. Di Excel modern dan Google Sheets, Anda cukup menekan Enter dan rumus akan berfungsi.

Di Excel versi lama (sebelum adanya array dinamis), Anda harus mengonfirmasinya sebagai rumus array dengan Ctrl+Shift+Enter, yang menambahkan kurung kurawal. Jika hasil Anda salah atau menampilkan kesalahan di Excel lama, langkah konfirmasi tersebut biasanya adalah bagian yang terlewat.

Memerlukan tiga kriteria? Kalikan saja dengan pengujian lainnya. Misalnya, Anda juga ingin mencocokkan warna di kolom D dengan input G3.

Setiap faktor (range=criterion) tambahan semakin mempersempit hasil. Hanya baris yang semua kondisinya TRUE yang mempertahankan hasil kali 1; satu saja FALSE akan mengubah seluruh hasil kali menjadi 0.

Pola ini dapat diperluas ke sebanyak kolom yang Anda perlukan.

Data: A = produk, B = ukuran, C = harga. Anda ingin mengetahui harga "Shirt" dalam ukuran "Large".

• G1 = "Shirt", G2 = "Large".
• Array kondisi menghasilkan angka 1 hanya pada baris Shirt+Large, misalnya baris 4.
• MATCH(1, ..., 0) mengembalikan 4.
• INDEX(C2:C10, 4) mengembalikan harga pada baris tersebut.

Ubah salah satu input dan rumus akan segera menemukan kembali baris yang tepat.

Ingat hal-hal berikut:

• Rentang yang sama: setiap rentang kondisi dan kolom INDEX harus memiliki tinggi yang sama.
• Tidak ada kecocokan: jika tidak ada baris yang memenuhi semua kriteria, MATCH mengembalikan #N/A. Bungkus seluruh rumus dengan IFERROR.
• Duplikat: jika lebih dari satu baris cocok, MATCH hanya mengembalikan yang pertama. Buat kriteria Anda cukup spesifik agar hasilnya unik.

Jika beberapa baris dapat cocok dan Anda lebih ingin menjumlahkan nilainya daripada mengambil satu nilai, SUMPRODUCT merupakan alternatif yang rapi untuk INDEX-MATCH yang dimasukkan sebagai array.

Fungsi ini mengalikan array kondisi dengan kolom nilai lalu menjumlahkan hasilnya, sehingga hanya baris yang memenuhi kedua kriteria yang berkontribusi. Anda tidak perlu menggunakan Ctrl+Shift+Enter karena SUMPRODUCT menangani array secara bawaan.

Gunakan INDEX-MATCH untuk mengambil satu nilai yang cocok; gunakan SUMPRODUCT untuk menggabungkan nilai dari semua kecocokan.

Uji pengetahuan Anda tentang pencarian dengan beberapa kriteria.

Untuk pencarian dengan beberapa kriteria menggunakan INDEX-MATCH:

• Kalikan array kondisi: (A=G1)*(B=G2) menghasilkan 1 hanya pada posisi yang memenuhi semuanya (logika AND).
• MATCH(1, ..., 0) menemukan posisi baris tersebut.
• INDEX(returnCol, position) mengembalikan nilainya.

Tambahkan faktor *(range=criterion) untuk kondisi tambahan, pastikan tinggi semua rentang sama, konfirmasikan dengan Ctrl+Shift+Enter di Excel versi lama, dan lindungi dengan IFERROR.