Rumus VLookup dan Index Match Excel

By | May 14, 2016

Rumus VLookup merupakan formulasi dari fungsi vlookup yang dapat digunakan untuk mencari pasangan data sel tertentu pada baris dan kolom yang terdapat dalam tabel data pada Excel (tabel referensi data). Dalam prakteknya tidak hanya rumus VLookup saja yang dapat digunakan dalam me-lookup pasangan data suatu sel (secara vertikal). Ada fungsi gabungan lain yang dapat melakukan tugas yang sama yaitu rumus Index Match. Biasa nya fungsi VLookup dan rumus Index-Match ini digunakan untuk melakukan pengisian otomatis pada pasangan data yang diisi / dipilih dalam satu sel pada halaman kerja Excel.

Bagaimana kedua fungsi dan rumus (VLookup dan Index Match) ini dapat bekerja dalam mencari pasangan data nya?

Pertama-tama kita perlu tahu :

Rumus VLookup

 

Untuk mencari pasangan data pada suatu sel dapat digunakan fungsi dan rumus excel vlookup :

VLOOKUP ( nilai_lookup; data_tabel_array; nomor_index_kolom; kriteria_lookup );

rumus VLookup-MatchIndex-1

Pada contoh di atas Data 1 (B6 sampai D20) merupakan data tabel array yang akan dijadikan data referensi (diambil datanya). DataArray1 merupakan range name yang dibuat dari Data 1 beserta informasi baris dan kolom nya (dari B6 sampai D20).

Lalu dibuat rumusnya pada :

H6=VLOOKUP(G6;DataArray1;3;FALSE);

Pada baris H6 akan ditampilkan nilai pasangan data untuk ID yang bernilai 4 (G6).

Terlihat pada sel H6 muncul pasangan ID yang bernilai 4 adalah kata baju sesuai dengan kata baju pada D9 pada DataArray1 (Data 1).

Catatan:

kriteria_lookup dibuat FALSE, artinya jika Data yang dibandingkan tidak persis sama maka akan muncul pesan N/A.

Selanjutnya kita pelajari :

 

Rumus MATCH

 

Untuk mencari posisi teks pada suatu array baris atau array kolom, gunakan fungsi dan rumus excel :

MATCH (nilai_lookup; data_array;kriteria_lookup)

VLookup-MatchIndex-2

Pada contoh di atas posisi teks yang ingin dicari adalah posisi kata baju (pada J6). Posisi kata baju terdapat pada baris ke-4 dimulai dari D6 s/d D20 yaitu pada sel D9 yang ditampilkan pada sel K6.

K6=MATCH(J6;D6:D20;0)

Catatan :

kriteria_lookup = 0 artinya nilai lookup harus persis sama dengan teks yang dicari, jika tidak akan bernilai error atau #N/A.

Setelah fungsi Match fungsi selanjutnya yang perlu kita tahu :

Rumus INDEX

 

Untuk menampilkan data entri di baris dan kolom yang terdapat dalam suatu array bilangan (data array), gunakan fungsi dan rumus excel :

INDEX (data_array; nomor_index_baris; nomor_index_kolom)

VLookup-MatchIndex-3

Pada contoh di atas teks yang ingin dicari adalah teks pada posisi ke-4 (pada array D6:D20). Teks pada baris ke-4 dimulai dari D6 s/d D20 adalah kata baju yang ditampilkan pada sel J6.

J6= INDEX(D$6:D$20;4)

Catatan :

Karena data yang dicari hanya pada array baris maka pada penulisan rumus pada contoh di atas, nomor_index_kolom dikosongkan.

Gabungan Fungsi Index dan Match dapat digunakan untuk melakukan lookup seperti pada fungsi VLookup, dengan cara berikut :

Rumus Index Match

 

Untuk mencari pasangan data pada suatu sel, selain Vlookup dapat juga digunakan fungsi gabungan (rumus excel) :

INDEX (data_array; MATCH (nilai_lookup; data_array; kriteria_lookup); nomor_index_kolom)

VLookup-MatchIndex-4

Pada contoh di atas Data pada kolom B (B6 sampai B20) dan data pada kolom D (D6 sampai D20) merupakan data array yang dijadikan data referensi (diambil datanya). Perumusan Index Match pada O6, untuk mencari pasangan data pada G6 adalah:

O6= INDEX(D6:D20;MATCH(G6;B6:B20;0))

Sama dengan hasil pada fungsi Vlookup, pada baris O6 akan ditampilkan nilai pasangan data untuk ID yang bernilai 4 (G6).

Terlihat pada sel O6 muncul pasangan ID yang bernilai 4 adalah kata baju sesuai dengan kata baju pada D9 pada DataArray1 (Data 1).

Catatan:

Karena data yang dicari hanya pada array baris maka pada penulisan rumus pada contoh di atas, nomor_index_kolom dikosongkan.

Jika diperhatikan kembali penggunaan fungsi dari rumus di atas, dapat dilihat bahwa pada penggunaan rumus Vlookup, untuk mencari pasangan data yang akan di-lookup, semua data pada tabel DataArray1 di-load oleh excel, perhatikan rumus fungsi H6=VLOOKUP(G6;DataArray1;3;FALSE). DataArray1 diload sehingga data pada kolom Person yang seharusnya tidak diperlukan ikut di-load. Sedang pada fungsi Index Match tidak semua data diload. Hanya data pada kolom tertentu saja yang dipilih. Perhatikan rumus nya pada O6= INDEX(D6:D20;MATCH(G6;B6:B20;0)).

Dari sisi performa, mungkin ini tidak terlalu berpengaruh jika data yang Anda gunakan tidak terlalu besar jumlahnya. Tetapi jika data yang Anda gunakan ribuan sampai puluhan ribu data, performanya akan sangat terasa. Penggunaan fungsi Vlookup untuk data yang besar dapat lebih memperlambat kinerja excel Anda.

Perbedaan lain adalah ketika melakukan look-up menggunakan fungsi INDEX dan MATCH, lookup dapat dilakukan dari kiri ke kanan atau dari kanan ke kiri (reverse) hanya dengan mengubah array pada fungsi INDEX dan MATCH. Pada contoh di atas kita telah mencoba untuk melakukan lookup data pasangan ID yang menghasilkan kata baju. Jika dengan kondisi sebaliknya Anda ingin me-lookup data pasangan Item, yaitu ID, Anda dapat melakukannya dengan mengubah referensi array dan sel yang akan dicari pasangannya.

Sedang pada fungsi Vlookup, Anda tidak dapat melakukan reverse lookup dengan mengubah hanya pada perumusan. Anda harus melakukan perubahan posisi data untuk melakukan reverse lookup jika menggunakan Vlookup. Jadi dengan Vlookup, lookup hanya bisa dilakukan dari kiri ke kanan.

Namun dalam penggunaannya tentunya akan lebih sederhana ketika menuliskan rumus fungsi dengan menggunakan fungsi Vlookup dan mudah diingat. Hal ini dapat digunakan untuk penggunaan data yang tidak terlalu besar, dan tidak menuntut banyak perubahan. Tetapi bagi penggunaan data yang besar dan menuntut perubahan di kemudian hari, akan lebih bijak Anda menggunakan gabungan fungsi MATCH INDEX.

Baca juga artikel terkait : belajar menggunakan rumus excel vlookup

 
Share

One thought on “Rumus VLookup dan Index Match Excel

  1. fadli

    Selama ini saya menggunakan fungsi VLookup, tetapi data yang saya lookup tidak sampai ribuan..artikelnya mencerahkan, makasih udah sharing

     
    View Comment
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *