Index Match Excel: Cara Lebih Baik untuk Mencari Data

INDEX MATCH, gabungan dua fungsi Excel, INDEX() dan MATCH(), merupakan alternatif dari fungsi VLOOKUP() yang umum digunakan.

Dalam panduan ini, saya akan menjelaskan cara kerja INDEX() dan MATCH() secara terpisah maupun bersama, cara melakukan lookup vertikal dan horizontal, serta praktik terbaik menggunakan INDEX MATCH dengan contoh praktis.

Apa Itu INDEX MATCH di Excel?

INDEX MATCH adalah cara singkat untuk menyebut proses menanamkan satu fungsi ke dalam fungsi lainnya. Ini setara dengan konsep INDEX(MATCH()). Mari kita lihat cara keduanya bekerja secara terpisah dan bersama.

Fungsi INDEX() di Excel

Fungsi INDEX() di Excel mengembalikan nilai atau referensi ke nilai dari dalam suatu rentang atau tabel. Anda dapat menggunakannya dalam dua cara: bentuk array dan bentuk referensi.

Bentuk array

Anda dapat menggunakan bentuk array saat bekerja dengan tabel atau array tetap. Dalam format ini, INDEX() mengambil nilai berdasarkan nomor baris dan kolom yang Anda tentukan. Sintaksnya adalah:

INDEX(array, row_num, [column_num])Didukung oleh

Mari pahami argumen-argumen ini:

• array (wajib) adalah rentang atau array nilai yang Anda gunakan.

• row_num (wajib) adalah nomor baris dari mana nilai akan dikembalikan.

• column_num (opsional) adalah nomor kolom dari mana nilai akan dikembalikan.

Kita harus menentukan setidaknya salah satu, yaitu row_num atau column_num agar Excel dapat menemukan nilainya.

Namun, berikut beberapa catatan singkat yang perlu Anda ingat saat bekerja dengan sintaks ini:

• Jika array hanya memiliki satu baris atau satu kolom, Anda bisa melewati row_num atau column_num.

• Jika row_num dan column_num disertakan, INDEX mengembalikan nilai di titik perpotongannya.

• Jika Anda mengatur row_num atau column_num ke 0, Excel mengembalikan seluruh kolom atau baris (Anda perlu memasukkannya sebagai formula array untuk melihat hasilnya).

Bentuk referensi

Anda dapat menggunakan bentuk referensi saat bekerja dengan beberapa rentang dan perlu menentukan mana yang akan digunakan.

Sintaksnya adalah:

INDEX(reference, row_num, [column_num], [area_num])Didukung oleh

Mari pahami argumen-argumen ini:

• reference (wajib) digunakan untuk mereferensikan satu atau lebih rentang sel.

• row_num (wajib) adalah nomor baris di area yang dipilih.

• column_num (opsional) adalah nomor kolom di area yang dipilih.

• area_num (opsional) memberi tahu Excel rentang mana yang akan digunakan jika ada lebih dari satu.

Berikut beberapa poin tambahan yang harus Anda ingat:

• Jika area_num tidak diisi, Excel menggunakan rentang pertama secara default.

• Anda tidak bisa mencampur rentang dari lembar yang berbeda dalam bentuk ini; hal itu akan memicu error.

• Jika baris dan kolom sama-sama dihilangkan, fungsi mengembalikan seluruh area yang ditentukan.

Fungsi MATCH() di Excel

Fungsi MATCH() membantu Anda menemukan posisi suatu nilai dalam baris atau kolom. Alih-alih mengembalikan nilainya, fungsi ini memberi tahu letaknya dalam daftar.

Sintaksnya adalah:

MATCH(lookup_value, lookup_array, [match_type])Didukung oleh

Mari pahami argumen-argumen ini:

• lookup_value (wajib) adalah nilai yang ingin Anda temukan. Bisa berupa angka, teks, nilai logika (TRUE/FALSE), atau referensi ke sel.

• lookup_array (wajib) adalah rentang sel yang akan dicari oleh Excel.

• match_type (opsional) menentukan bagaimana Excel mencari kecocokan. Default-nya adalah 1. Di sini 1 menemukan nilai terbesar yang kurang dari atau sama dengan lookup_value (memerlukan urutan menaik). 0 menemukan kecocokan tepat (urutan apa pun). -1 menemukan nilai terkecil yang lebih besar dari atau sama dengan lookup_value (memerlukan urutan menurun).

Fungsi MATCH tidak peka huruf besar/kecil, jadi memperlakukan huruf besar dan kecil sama. Jika Anda bekerja dengan teks dan ingin menggunakan wildcard seperti * (sejumlah karakter apa pun) atau ? (satu karakter), pastikan match_type diatur ke 0. Karena itu satu-satunya mode yang mendukung pencarian wildcard.

Bagaimana INDEX() dan MATCH() Bekerja Bersama di Excel?

Salah satu penggunaan MATCH() yang paling umum adalah dipasangkan dengan fungsi INDEX(). Sementara MATCH() menemukan posisi suatu nilai, INDEX() mengambil nilai pada posisi tersebut. 

Menanamkan MATCH() di dalam INDEX() memberi Anda formula yang dinamis dan tahan perubahan saat kolom atau baris bergeser seiring waktu. 

Sebagai contoh, saya memiliki dataset contoh. Dan jika saya ingin mencari apa yang ada pada baris yang sama di kolom B seperti Banana di kolom A, saya dapat menggunakan INDEX MATCH bersama seperti ini:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))Didukung oleh

Di sini, MATCH("Banana", A2:A5, 0) menemukan posisi Banana di kolom A dan INDEX(B2:B5, ...) mengembalikan nilai dari baris yang sama di kolom B.

INDEX MATCH vs. VLOOKUP(): Mana yang Harus Anda Gunakan?

INDEX MATCH dapat melakukan hal-hal yang VLOOKUP() tidak bisa. Namun, pemilihan di antara keduanya bergantung pada tingkat fleksibilitas dan kinerja yang Anda butuhkan di lembar Excel Anda. Mari kita bandingkan.

Fleksibilitas kolom dan integritas data

Salah satu kelemahan terbesar VLOOKUP() adalah ketergantungannya pada nomor kolom yang tetap. Jika Anda menyisipkan atau memindahkan kolom, formula Anda bisa rusak.

Ambil contoh ini:

Jika kolom baru ditambahkan di antara Kolom A dan Kolom B, kolom ketiga bergeser, dan formula mengembalikan hasil yang salah, seperti yang terlihat pada gambar di bawah.

=VLOOKUP("Apple", A2:C5, 2, FALSE)Didukung oleh

INDEX MATCH menghindari masalah ini dengan bekerja pada rentang yang ditentukan alih-alih posisi kolom yang dikodekan keras:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))Didukung oleh

Bahkan jika kolom diatur ulang, formula ini tetap efektif, sehingga lebih andal dan mudah dirawat. Di sini, sebuah kolom disisipkan sebelum kolom B; formula diperbarui secara otomatis, dan hasilnya tetap benar. Inilah keunggulan INDEX MATCH.

Arah lookup dan daya guna

VLOOKUP() hanya bisa mencari dari kiri ke kanan. Artinya kolom lookup Anda harus selalu menjadi kolom pertama dalam rentang. Jika nilai yang Anda butuhkan berada di sebelah kiri kolom lookup, VLOOKUP() tidak dapat menemukannya.

Namun, jika kita mencari berdasarkan nama buah untuk menemukan warnanya di sebelah kanan, formula ini akan bekerja. Misalnya, jika kita ingin menemukan warna pisang dari daftar, hasilnya akurat: 

Dengan INDEX MATCH, keterbatasan itu tidak ada. Anda bisa mencari ke arah mana pun: kiri, kanan, atas, atau bawah. Ini juga mendukung lookup horizontal untuk memberi Anda lebih banyak kendali atas cara Anda mengatur data. 

Mari terapkan INDEX MATCH pada contoh ini dan lihat bagaimana ia memungkinkan lookup ke kiri dan ke kanan.

Untuk lookup ke kiri, Anda dapat menggunakan formula berikut, dan hasilnya akan akurat: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))Didukung oleh

Untuk lookup ke kanan, Anda dapat menggunakan formula berikut, dan hasilnya akan benar: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))Didukung oleh

Performa dan efisiensi di Excel

VLOOKUP() memindai seluruh tabel untuk menemukan kecocokan dan mengambil nilai dalam satu langkah. Namun INDEX MATCH membagi tugas: MATCH() menemukan baris atau kolom, dan INDEX() mengambil nilainya. 

Ini membuatnya lebih cepat dan lebih hemat memori, terutama pada workbook yang kompleks atau berukuran besar.

Berikut tabel untuk memberi Anda gambaran lebih baik tentang fungsi mana yang mendukung fungsionalitas apa:

Kapan menggunakan INDEX MATCH

Anda dapat menggunakan INDEX MATCH ketika:

• Anda bekerja dengan dataset besar di mana kecepatan penting
• Model Anda dinamis, dengan kolom yang ditambahkan atau diatur ulang
• Anda perlu mencari nilai ke kiri atau dalam rentang horizontal

Jika file Excel Anda kecil dan sederhana, VLOOKUP() mungkin masih memadai. Namun untuk pemodelan dan pelaporan tingkat lanjut, INDEX MATCH lebih aman.

Cara Menggunakan INDEX MATCH Langkah demi Langkah

Misalkan Anda memiliki dua kolom: Fruits dan Sales. Anda ingin mencari nilai penjualan untuk "Apple."

Untuk itu, ketik formula gabungan INDEX MATCH dan tekan Enter:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Didukung oleh

Di sini, MATCH("Apple", A2:A6, 0) menemukan posisi "Apple" dalam daftar Fruits . Dan INDEX(B2:B6, ...) menggunakan posisi tersebut untuk mengembalikan nilai penjualan yang sesuai dari kolom Sales.

Anda dapat melihat formula mengembalikan 12000, yaitu nilai penjualan untuk Apple. Jadi, itulah hasilnya.

Menghindari Error INDEX MATCH

Meskipun INDEX MATCH adalah kombinasi yang baik, tetap bisa memunculkan error jika tidak disetel dengan benar. Berikut cara menelusuri masalah paling umum dan menghindari kesalahan yang dapat merusak formula Anda.

Error #N/A dan cara mengatasinya

Error #N/A berarti Excel tidak menemukan nilai yang Anda cari. Beberapa penyebab umumnya antara lain:

• Salah ketik, spasi berlebih, atau ketidaksesuaian teks (misal, "apple" vs. "Apple")

• Tidak ada nilai dalam rentang lookup

• match_type yang salah, misalnya 1 alih-alih 0 untuk kecocokan tepat

• Karakter tersembunyi (seperti non-breaking space) dalam data

Ada dua cara untuk memperbaikinya:

Gunakan TRIM() untuk membersihkan spasi di depan atau di belakang:

=MATCH(TRIM("Apple"), A2:A6, 0)Didukung oleh

Tambahkan IFERROR() untuk menampilkan nilai pengganti alih-alih error:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")Didukung oleh

Error #REF! dalam formula INDEX MATCH

Error #REF! biasanya menunjukkan referensi yang rusak—sering kali karena:

• Ketidakcocokan jumlah baris pada rentang INDEX() dan MATCH() Anda

• Baris atau kolom yang dihapus dan digunakan dalam formula Anda

• Menyalin dan menempel formula tanpa memperbarui referensi sel

Namun ada beberapa cara mudah untuk memperbaikinya: 

• Pastikan rentang INDEX() dan MATCH() berukuran sama:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Didukung oleh

• Kunci rentang dengan referensi absolut (seperti $A$2:$A$6) agar formula tetap stabil saat salin-tempel.

• Hindari menghapus sel yang menjadi acuan formula.

Performa lambat dan dataset besar

Jika Anda bekerja dengan ribuan baris, formula INDEX MATCH yang tidak dioptimalkan dapat memperlambat Excel.

Berikut cara mengoptimalkannya:

• Batasi rentang lookup alih-alih mereferensikan seluruh kolom:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))Didukung oleh

• Hindari fungsi volatil seperti OFFSET(), INDIRECT(), dan NOW() di dekat lookup Anda.

• Gunakan Excel Tables atau named range untuk menjaga kerapian dan meningkatkan performa.

Mengelola error spill di Excel 365

Excel 365 memperkenalkan dynamic array, yang dapat “menyebarkan” hasil ke sel di sekitarnya. Jika ada masalah pada proses tersebut, Anda akan mendapatkan error #SPILL!.

Beberapa penyebab umum bisa berupa:

• Sel di sekitar tidak kosong.
• Formula mengembalikan banyak nilai ke rentang sel yang terblokir atau digabung.
• Anda menggunakan dynamic array saat hanya satu nilai yang dibutuhkan.

Namun masalah ini bisa diperbaiki. Berikut cara mengatasinya:

• Kosongkan sel di bawah atau di samping formula.
• Batalkan penggabungan sel di area spill.
• Jika Anda hanya menginginkan satu nilai, paksa satu hasil dengan @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Didukung oleh

Penutup

Jika Anda selama ini mengandalkan VLOOKUP(), sekarang saatnya beralih ke yang lebih stabil. Anda bisa mulai dengan beberapa lookup sederhana menggunakan INDEX MATCH pada salinan file kerja Anda. Cobalah mengganti formula lama di tempat perubahan kolom menyebabkan error atau saat struktur data Anda membutuhkan fleksibilitas. Setelah melihat bagaimana ia menangani kasus-kasus tersebut dengan mulus, Anda akan enggan kembali.