Menggunakan Rumus Index Match Excel Pengganti Vlookup

Dalam prakteknya tidak hanya fungsi 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 :

Contents

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 );

Pada contoh di atas Data 1 (B6 sampai D20) merupakan data tabel array yang akan dijadikan data referensi (diambil datanya). DataArray1 merupakan nama area (range name) yang dibuat dari Data 1 (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 (D6:D20).

Catatan:

kriteria_lookup dibuat FALSE, artinya jika Data yang dibandingkan tidak persis sama maka data yang dipasangkan tidak akan muncul atau akan muncul pesan error.

tabel array => tabel pembacaaan adalah range data yang disusun secara vertikal dan horizontal yang berfungsi sebagai tabel yang akan dibaca.

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)

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)

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)

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.

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 (diikutsertakan) 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 (diikutsertakan). 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 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 INDEX dan MATCH.

Author
Recent Posts

Edwin

Author at Media Aplikatif

Penulis adalah seorang profesional yang berpengalaman dalam manajemen proyek dan analisa jadwal proyek. Dia memiliki latar belakang dalam bidang teknik dan telah bekerja di berbagai proyek konstruksi.

Penulis berkomitmen untuk terus berbagi pengetahuannya dengan komunitas melalui blog tutorialnya.

Tujuannya adalah untuk membantu pembaca meningkatkan efisiensi, produktivitas, dan keberhasilan dalam proyek-proyek dengan memanfaatkan alat-alat perangkat lunak yang telah terbukti efektif.

Dengan cara ini, Penulis berharap dapat memberikan kontribusi positif bagi perkembangan karir dan kesuksesan profesional pembacanya di bidang manajemen proyek.