Pencarian Multi Variable dengan Match dan Index Excel

Pertanyaan

Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:

• Kolom 1 : Berupa Kode/ID
• Kolom 2 : Berupa tipe konsumen (reseller/langsung)
• Kolom 3 : Harga barang.

di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:

• Kolom 1: Tanggal
• Kolom 2: Kode/ID
• Kolom 3: Tipe konsumen

Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.

Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Jawab

Solusi untuk masalah ini adalah fungsi yang digunakan adalah Index dan Match, tetapi dengan pendekatan Array Formula. Berikut adalah rincian jawabannya.

Untuk menjawab pertanyaan ini, kami menggunakan file yang dapat Anda download pada alamat http://adf.ly/MBQfJ. Workbooknya akan terlihat sebagai berikut.

Workbook ini memiliki sheet "data" dan "master_harga" (klik pada tab dengan nama sheet tersebut untuk melihat rincian dari tiap sheet).



Sekarang kita akan mengisi kolom Harga yang didapatkan dari pencarian kombinasi kolom Kode / ID dan Tipe ke sheet master_harga. (klik gambar untuk memperbesar)

Berikut adalah langkah-langkah untuk melakukan kombinasi pencarian tersebut :

1. Jalankan aplikasi Microsoft Excel 2007 atau ke atas.
2. Buka file yang Anda download tersebut.
3. Pada sheet data, klik cell pada alamat D2 dan masukkan rumus di bawah ini. Perhatikan bahwa kita menggunakan operator dan ( & ) untuk menggabungkan cell B dan C (kolom Kode / ID dan Tipe).
   
   =INDEX(master_harga!$C$2:$C$11,MATCH(B2&C2,master_harga!$A$2:$A$11&master_harga!$B$2:$B$11))
   
   Tekan CTRL + SHIFT + ENTER untuk menyelesaikan formula tersebut (bukan Enter seperti biasanya).
   
   
4. Hasilnya akan tampak seperti pada gambar screenshot sebagai berikut. Perhatikan bahwa rumus yang kita masukkan diapit oleh kurung kurawal {}. Ini pertanda bahwa formula tersebut adalah array formula.
5. Copy rumus tersebut ke kolom Harga yang masih kosong.
   
   
6. Selesai.

Referensi

• Sumber Pertanyaan : Komentar pada artikel Match dan Index.
• http://superuser.com/questions/563617/find-array-intersection-of-2-match-array-formula/564035?noredirect=1#564035