Menggali Informasi Dengan Rumus INDEX MATCH

Pada beberapa artikel sebelumnya sudah dibahas cara mencari atau menemukan data tertentu menggunakan rumus VLOOKUP. Dalam kesempatan ini kita akan membahas salah satu rumus alternative unggulan lainnya yang juga dapat digunakan untuk mencari data dan menggali informasi tertentu dari sebuah tabel data. Ialah rumus INDEX MATCH, yang oleh banyak pakar excel sangat direkomendasikan sebagai pengganti VLOOKUP karena beberapa poin keunggulannya. Yuk kita pelajari dengan seksama.

Contoh Soal.

Tersedia sebuah tabel data karyawan terdiri atas 5 kolom. 

• Kolom ke-1  : Nama
• Kolom ke-2  : NIK
• Kolom ke-3  : Jenis Kelamin
• Kelom ke-4  : Tgl Lahir
• Kolom ke-5  : Asal Kota

Selanjutnya kita diminta untuk menggali informasi karyawan dari tabel yang sudah disediakan. Adapun kata kunci untuk mencari informasi data tersebut adalah berupa NIK, dimana untuk NIK ini sudah disediakan kolom input secara manual. Sedangkan untuk informasi lainnya harus diformulasikan supaya bisa muncul secara otomatis, cepat dan akurat ketika NIK diinput.

Untuk lebih jelasnya contoh soal ini dapat dilihat dalam screenhot di bawah ini:

Contoh Rumus INDEX MATCH Untuk Menggali Informasi Karyawan.

Sebelum melanjutkan. Silahkan diperhatikan terlebih dahulu gambar tabel contoh di atas. Kata kunci yang kita gunakan adalah NIK, dan ternyata NIK terletak pada kolom ke-2 dalam tabel data karyawan. Pola tabel seperti ini tentu saja bukan kondisi yang ideal untuk rumus VLOOKUP, kecuali dengan bantuan fungsi CHOOSE seperti yang sudah dijelaskan dalam pembahasan Rumus  VLOOKUP ke kiri.

Sebagai solusi rumus pencarian data, maka disini kita akan gunakan rumus INDEX MATCH. Sebagaimana tersurat jelas dari namanya, formula ini merupakan gabungan dari fungsi INDEX dan MATCH.

Fungsi INDEX digunakan untuk mengambil data dari nomor urut baris dan nomor kolom tertentu dalam sebuah range. 

Fungsi ini dapat dituliskan sebagai berikut:

=INDEX(range, nomorBaris, nomorKolom)

Jika range hanya terdiri atas satu baris saja, maka parameter nomor baris bisa ditiadakan sehingga rumus umumnya bisa diubah menjadi:

=INDEX(rangeBaris,nomorKolom)

Jika range hanya terdiri atas satu kolom saja, maka argumen yang diperlukan cukup berupa range kolom dan nomor baris. Rumusnya dapat dituliskan sebagai berikut:

=INDEX(rangeKolom,nomorBaris)

Rumus terakhir di atas ini yang akan kita gunakan untuk menyelesaikan kasus sesuai contoh soal yang sudah diberikan. 

Misalnya kita ingin mendapatkan informasi data nama pada kolom Nama (range A2:A6), maka kita bisa menuliskan rumus berikut:

=INDEX(rangeKolom,nomorBaris) = Hasil

=INDEX(A2:A6,1) ⇒ Hasil = "Dani Ramdan"

=INDEX(A2:A6,2) ⇒ Hasil = "Badu Baidu"

=INDEX(A2:A6,3) ⇒ Hasil = "Ratna Anjani"

=INDEX(A2:A6,4) ⇒ Hasil = "Galih Prima"

=INDEX(A2:A6,5) ⇒ Hasil = "Dewi Yuli"

Contoh-contoh rumus diatas menunjukan hasil yang diperoleh bergerak dari atas ke bawah sesuai nomor urut baris 1,2,3,4,5 dalam range A2:A6.

Namun secara praktis, rumus-rumus tersebut kurang mengena. Kenapa demikian? Karena kita harus mengeikan nomor urut baris secara manual. Padahal dalam prakteknya justru kita ingin mencari nomor urut baris tersebut supaya diperoleh informasi yang tepat.

Disinilah peranan fungsi MATCH

Fungsi MATCH digunakan untuk mencari sebuah nilai atau kata kunci dalam sebuah range kolom dan menghasilkan nomor urut baris dalam kolom dimana kata kunci tersebut ditemukan.

Fungsi MATCH dapat dituliskan sebagai berikut:

=MATCH(kataKunci,rangeKolom,0) 

Contoh: kita bisa mencari nomor urut NIK 12562 ada kolom NIK (range B2:B6) dengan rumus berikut:

=MATCH(12562,B2:B6,0) ⇒ Hasil = 3 (baris ke-3)

Kemampuan fungsi MATCH ini yang bila dikombinasikan dengan fungsi INDEX makan akan menjadikannya rumus yang sangat powerfull dalam tugas pencarian data.

Secara umum, formula pencarian data menggunakan fungsi INDEX MATCH dapat dituliskan sebagai berikut:

=INDEX(rangeKolomHasil,MATCH(kataKunci,rangeKolomKunci,0))

Jika diterapkan pada contoh soal, maka parameter-parameter tersebut bisa dirinci sebagai berikut:

• rangeKolomHasil adalah kolom Nama (range A2:A6), kolom Jenis Kelamin (range C2:C6), kolom Tgl Lahr (range D2:D6) dan kolom Asal Kota (rangen E2:E6)
• kataKunci adalah nilai yang sudah diketahui dan akan dicari informasi lainnya yang terkait dengan kata kunci tersebut. Dalam hal ini berupa NIK yang sudah diketahui (cell C10)
• rangeKolomKunci adalah kolom dalam tabel dimana kata kunci berada (range B2:B6)

Selanjutnya dengan menggunakan referensi tersebut, kita bisa menuliskan rumus berikut:

Mencari Nama

=INDEX(A2:A6,MATCH(C10,B2:B6,0))

Mencari Jenis Kelamin

=INDEX(C2:C6,MATCH(C10,B2:B6,0))

Mencari Tanggal Lahir

=INDEX(C2:C6,MATCH(C10,B2:B6,0))

Mencari Asal Kota

=INDEX(E2:E6,MATCH(C10,B2:B6,0))

Penerapannya dalam spreadsheet excel dapat dilihat dalam screenshot di bawah ini:

Catatan:

Referensi rangeKolomHasil dan rangeKolomKunci dianjurkan untuk memiliki jumlah baris yang sama dan sejajar untuk meminimalisir kesalahan hasil yang diperoleh

Sampai disini pembahasan mengenai contoh rumus pencarian data secara cepat dan akurat menggunakan rumus INDEX Match. Semoga bermanfaat dan dapat diterapkan dalam pekerjaan sehari-hari.

Contoh Rumus Excel Lainnya

• Rumus VLOOKUP Untuk Apa Sich?
• Rumus VLOOKUP Untuk Membuat Kode Barang
• Rumus VLOOKUP Untuk Membaca Kode Barang
• Rumus VLOOKUP Ke Kiri

Rumus VLOOKUP 2 Dimensi

Rumus VLOOKUP 2 dimensi bekerja secara dua arah, baik vertikal maupun horizontal. Rumus ini sangat berguna untuk mencari data yang berubah secara periodik. Misalnya data nilai penjualan yang berubah setiap bulannya. Dengan rumus VLOOKUP dua dimensi maka kita bisa mencari data nilai penjualan pada bulan tertentu secara mudah dan cepat. Untuk tugas tersebut, fungsi VLOOKUP tidak bisa bekerja sendiri, namun harus dibantu oleh fungsi lainnya, yakni fungsi MATCH.

Contoh soal:

Tersedia sebuah tabel berisi data penjualan produk buah-buahan selama beberapa bulan. Nama produk buah-buahan disusun dalam dimensi vertikal. Sedangkan data per bulannya disusun dalam dimensi horizontal. Untuk lebih jelas mengenai gambaran soalnya, silahkan diperhatikan gambar di bawah ini.

Tugas kita selanjutnya adalah merumuskan formula yang tepat untuk mencari nilai penjualan produk buah tertentu pada bulan tertentu pula.

Misalnya: Berapa nilai penjualan buan Apel pada Bulan Februari?

Secara kasat mata, dengan hanya menampilkan data tabel di atas, sebenarnya kita bisa langsung mengetahui bahwa penjualan buah apel pada bulan Februari adalah 500. 

Bagaimana kita bisa langsung mengatakan bahwa penjualan buah Apel pada bulan Februari adalah senilai 500?

Mudah saja, karena mata kita akan melihat garis temu antara garis apel secara horizontal dan kolom Februari secara vertikal, kemudian otak kita menyimpulkan bahwa titik temu tersebut adalah sebuah sel dengan konten nilai 500. Analogi inilah yang kita gunakan dalam logika rumus excel VLOOKUP 2 Dimensi untuk mencari titik temu antara baris kriteria1 dan kolom kriteria2.

Contoh Rumus VLOOKUP 2 Dimensi Untuk Mencari Nilai Penjualan Sebuah Produk Pada Bulan Tertentu.

Secara umum rumus untuk tujuan dimaksud dapat dituliskan sebagai berikut:

=VLOOKUP(produk,rangeTabel,MATCH(bulan,rangeHeader,0),0)

• Produk = nama produk yang akan dicari nilai penjualannya pada bulan tertentu (Contoh : buah "Apel")
• rangeTabel = referensi range tabel data, boleh tanpa header (contoh = range A2:D4), namun bisa juga termasuk header (range A1:D4)
• bulan = nama bulan (Contoh = "Februari")
• rangeHeader = referensi header kolom tabel. (Contoh = range A1:D1)

Sehingga untuk mencari nilai penjualan buah “Apel” pada bulan “Februari”, rumusnya dapat dituliskan sebagai berikut:

=VLOOKUP("apel",rangeTabel,MATCH("Februari",rangeHeader,0),0)

atau

=VLOOKUP("apel",A2:D4,MATCH("Februari",A1:D1,0),0)

Dan hasilnya = 500

Cara kerja rumus VLOOKUP 2 dimensional untuk mencari nilai penjualan sebuah produk pada bulan tertentu.

Untuk memahami rumus di atas, mari kita bedah dan pecah rumus menjadi fungsi VLOOKUP dan Fungsi MATCH.

• Fungsi VLOOKUP bekerja pada dimensi Vertikal.
• Fungsi MATCH bekerja pada dimensi horizontal (untuk kasus dalam contoh, secara aplikatif fungsi MATCH juga bisa bekerja secara vertikal)

Sebagaimana kita ketahui bahwa fungsi VLOOKUP berguna untuk mencari data pada kolom pertama sebuah tabel dan mendapatkan nilai dari nomor urut kolom tertentu yang sebaris dengan data ditemukan di kolom pertama.

Misalnya:

=VLOOKUP("apel",A2:D4,nomorKolom,0)

Rumus tersebut akan mencari "apel" pada kolom pertama dari range A2:D4

Setelah "apel" ditemukan di kolom pertama, kemudian VLOOKUP akan menoleh ke sebelah kanan menuju nomorKolom tertentu yang ditentukan, dan mendapatkan nilainya.

Selandainya kita ganti argumen nomorKolom menjadi 1, 2, 3, dan 4 maka rumusnya akan seperti ini.

=VLOOKUP("apel",A2:D4,1,0) ⇒ hasil = "Apel" 

=VLOOKUP("apel",A2:D4,2,0) ⇒ hasil = 850

=VLOOKUP("apel",A2:D4,3,0) ⇒ hasil = 500

=VLOOKUP("apel",A2:D4,4,0) ⇒ hasil = 750

Dapat kita lihat hasil dari rumus - rumus di atas. Ternyata hasil dari rumus pertama sampai ke-4 bergerak dari kiri ke kanan pada baris apel sesuai nomor urut kolomnya.

Kembali ke pertanyaan awal: Bagaimana mencari nilai penjualan produk Apel di bulan Februari. Kita ketahui bulan Februari merupakan kolom ke-3. Nah bagaimana supaya rumus bisa mengenali bahwa bulan Februari itu merupakan kolom ke-3? Disinilah peranan fungsi MATCH.

Mari kita perhatikan kembali rumus berikut ini

=MATCH("Februari",rangeHeader,0)

Rumus tersebut berguna untuk mencari nomor urut kolom "Februari" pada rangeHeader. Jika range headernya kita ganti dengan range A1:D1 maka rumusnya menjadi 

=MATCH("Februari",A1:D1,0)

Dan hasilnya adalah 3, karena memang ("Februari") terletak pada kolom ke-3 dalam range A1:D1. 

Kemampuan fungsi MATCH ini untuk mendapatkan nomor urut kolom tertentu pada sebuah tabel maka fungsi ini dapat bekerjasama dengan fungsi VLOOKUP dalam rumus pencarian data 2 dimensi. 

Fungsi MATCH menyediakan informasi nomorKolom yang dapat digunakan selanjutnya oleh fungsi VLOOKUP. 

=VLOOKUP("apel",rangeTabel,nomorKolom,0)

Menjadi:

=VLOOKUP("apel",rangeTabel,MATCH("Februari",rangeHeader,0),0)

Bila argument-argumen rumus tersebut kita ganti dengan referensi sesuai contoh soal.

• Nama buah = sel D7
• Nama Bulan = Sel D8
• rangeTabel = A2:D4, atau A1:D4
• rangeHeader = A1:D1

Maka rumusnya (di sel D9) menjadi seperti berikut:

=VLOOKUP(D7,A2:D4,MATCH(D8,A1:D1,0),0)

Berikut screenshot penampakannya dalam spreadsheet:

Silahkan dicoba langsung dalam lembar kerja excel dan perhatikan hasilnya. Kemudian coba rubah nama buah (sel D7) dan nama bulan (sel D8) dan perhatikan lagi hasilnya. Kita akan melihat hasil rumus VLOOKUP 2 dimensi berubah mengikuti nilai penjualan produk buah pada bulan yang ditentukan.

Demikian pembahasan singkat mengenai contoh rumus VLOOKUP 2 Dimensi untuk mencari nilai penjualan sebuah produk pada bulan tertentu. Semoga penjelasannya mudah difahami.

Terimakasih.

Contoh Rumus Excel Lainnya:

• Rumus VLOOKUP untuk Apa Sich??
• Rumus VLOOKUP 2 Kriteria
• Rumus VLOOKUP untuk Mencari Nama Barang
• Rumus VLOOKUP untuk Tanggal