Formula umum
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Ringkasan
Untuk mencari nilai dengan INDEX dan MATCH, menggunakan beberapa kriteria, Anda bisa menggunakan rumus array. Pada contoh yang ditampilkan, rumus di H8 adalah:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Catatan: ini adalah rumus array, dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Penjelasan
Ini adalah rumus yang lebih canggih. Untuk dasar-dasar, lihat Cara menggunakan INDEX dan MATCH.
Biasanya, rumus INDEX MATCH dikonfigurasikan dengan kumpulan MATCH untuk mencari melalui rentang satu kolom dan memberikan kecocokan berdasarkan kriteria yang diberikan. Tanpa nilai gabungan dalam kolom pembantu, atau dalam rumus itu sendiri, tidak ada cara untuk menyediakan lebih dari satu kriteria.
Rumus ini mengatasi batasan ini dengan menggunakan logika boolean untuk membuat larik satu dan nol untuk mewakili baris yang cocok dengan semua 3 kriteria, lalu menggunakan MATCH untuk mencocokkan 1 yang pertama ditemukan. Array sementara satu dan nol dibuat dengan cuplikan ini:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Di sini kami membandingkan item di H5 terhadap semua item, ukuran di H6 terhadap semua ukuran, dan warna di H7 terhadap semua warna. Hasil awalnya adalah tiga larik hasil TRUE / FALSE seperti ini:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tip: gunakan F9 untuk melihat hasil ini. Cukup pilih ekspresi di bilah rumus, dan tekan F9.
Operasi matematika (perkalian) mengubah nilai TRUE FALSE menjadi 1 dan 0:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Setelah perkalian, kita memiliki satu larik seperti ini:
(0;0;1;0;0;0;0)
yang dimasukkan ke dalam fungsi MATCH sebagai array pencarian, dengan nilai pencarian 1:
MATCH(1,(0;0;1;0;0;0;0))
Pada titik ini, rumusnya adalah rumus INDEX MATCH standar. Fungsi MATCH mengembalikan 3 ke INDEX:
=INDEX(E5:E11,3)
dan INDEX mengembalikan hasil akhir $ 17,00.
Visualisasi larik
Array yang dijelaskan di atas mungkin sulit untuk divisualisasikan. Gambar di bawah ini menunjukkan ide dasarnya. Kolom B, C, dan D sesuai dengan data di contoh. Kolom F dibuat dengan mengalikan tiga kolom. Ini adalah array yang diserahkan ke MATCH.
Versi non-array
Dimungkinkan untuk menambahkan INDEX lain ke rumus ini, menghindari keharusan untuk memasukkan sebagai rumus array dengan control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Fungsi INDEX dapat menangani array secara native, sehingga INDEX kedua ditambahkan hanya untuk "menangkap" array yang dibuat dengan operasi logika boolean dan mengembalikan array yang sama lagi ke MATCH. Untuk melakukan ini, INDEX dikonfigurasi dengan baris nol dan satu kolom. Trik baris nol menyebabkan INDEX mengembalikan kolom 1 dari array (yang toh sudah menjadi satu kolom).
Mengapa Anda menginginkan versi non-array? Terkadang, orang lupa memasukkan rumus array dengan control + shift + enter, dan rumus tersebut mengembalikan hasil yang salah. Jadi, rumus non-larik lebih "antipeluru". Namun, pengorbanannya adalah rumus yang lebih kompleks.
Catatan: Di Excel 365, tidak perlu memasukkan rumus array dengan cara khusus.
