Menggunakan INDEX dan MATCH sebagai Advanced LOOKUP

Hai kawan-kawan saya sekalian.. ^^

Saya mengucapkan Gong Xi Fa Cai ya.. Wo de hong bao don't forget ^_-


Pada kesempatan kali ini saya membahas mengenai INDEX dan MATCH. Topik sebelumnya saya sudah membahas VLOOKUP dimana lookup_value hanya bisa dengan 1 nilai atau cell saja untuk dicari di table_array.  Namun dengan perpaduan fungsi INDEX dan MATCH, untuk mencari 2 nilai atau cell tidak lagi menjadi masalah.

1. MATCH
   Fungsi MATCH mengembalikan posisi secara relative (tergantung range area) dari 1 atau lebih nilai yang ingin dicari pada table array.
   

   Syntax :  =MATCH(lookup_value, lookup_array, [match_type])
   Lookup Value merupakan nilai yang bisa berupa value langsung (berupa angka atau huruf atau karakter lain) atau bisa juga berupa cell reference. Lookup Value merupakan parameter yang wajib disertakan.
   Lookup Array merupakan range yang akan dipakai sebagai sumber pencarian lookup value.Parameter ini juga wajib disertakan.
   Match Type merupakan parameter optional yang memberikan pilihan -1 Less Than, 0 Exact Match, dan 1 untuk Greater Than. Exact Match adalah cocok untuk mencari data yang dicocokkan secara exact.

2. INDEX
   Fungsi INDEX mengembalikan nilai dari sebuah range berdasarkan nomor kolom dan baris yang disertakan di dalam parameternya.
   Syntax : =INDEX (Array, Row_num, Column_num)
   Array : Range yang akan dipakai untuk diambil nilainya. Parameter ini wajib disertakan.
   Row Num : Nomor baris relatif yang akan dipilih dan wajib disertakan.
   Column_Num : Nomor kolom relatif yang akan dipilih untuk dipilih dan parameter ini wajib disertakan.

Contoh Kasus :

Gambar di bawah ini adalah Omzet yang dihasilkan oleh Salesman per kota (City). Kemudian kasusnya adalah kebutuhan mencari nilai omzet secara exact City, Salesman, dan Bulan tertentu.

Cell A2 untuk memilih kota, B2 memilih Salesman, dan C2 memilih Bulan.

Fungsi INDEX membutuhkan baris dan kolom, sedangkan fungsi MATCH bisa mengembalikan nilai nomor baris dan kolom. Dengan demikian, parameter nomor baris dan kolom diambilkan dari hasil eksekusi fungsi MATCH.

City Surabaya Salesman B ada di nomor baris 2 secara relative apabila range yang dipakai mulai cell A5.

Bulan Feb ada di kolom nomor 2 secara relative apabila range yang dipakai untuk mencari nomor kolom Bulan dimulai dari C5.

Maka dapat menggunakan MATCH untuk mencari nomor baris berdasarkan City dan Salesman.

=MATCH(A2&B2,A5:A54&B5:B54,0)

Lookup value diberikan A2&B2 maksudnya ialah nilai cell A2 digabungkan langsung dengan nilai cell B2.
Misal A2 adalah SURABAYA, B2 adalah B..Maka nilai A2&B2 adalah "SURABAYAB"

Table array nya juga demikian, valuenya harus ditata dengan format CITY&SALESMAN, sehingga A5:A54&B5:B54 akan menghasilkan "SURABAYAA";"SURABAYAB";"SURABAYAC"; dst..

Match type bernilai EXACT MATCH atau 0.

Nah rumus tersebut akan menghasilkan nomor baris yang menggunakan CITY dan SALESMAN.

Sekarang untuk mendapatkan nomor kolom kita akan menggunakan MONTH.
Rumus yang dipakai adalah : =MATCH(C2,C4:N4,0)

Lookup value berisikan nama bulan yang akan dicari di C4 sampai N4. Pada kasus ini adalah sangat sederhana dan tidak memerlukan "&" karena yang dicari hanyalah 1 nilai. Ingat, MATCH digunakan untuk mendapatkan nomor kolom sehingga tidak tepat digantikan dengan fungsi VLOOKUP (jika ini timbul sebagai pertanyaan kawan2 nih ^^ ).

Nomor kolom dan baris sudah bisa dihasilkan dengan 2 MATCH.
INDEXnya dapat menggunakan rumus sbb : =INDEX(C5:N54,MATCH(A2&B2,A5:A54&B5:B54,0),MATCH(C2,C4:N4,0))

Nah saya harap kawan-kawan berhasil mempraktikkan contoh kasus saya ini..
Selamat mencoba yaaaa... 

Terima kasih..

Kalau ada pertanyaan mengenai excel dapat mengirimkan email : IndoprimaMahirExcel@gmail.com