M365Con 2027 – konferensi online gratis · 11–22 Jan 2027 · 120 sessions m365con.net

Blog · Mirko Peters

INDEX MATCH di Excel: Rumus, Contoh, dan Kesalahan Umum

By · · Updated

INDEX MATCH di Excel dijelaskan: rumusnya, cara kerja setiap fungsi, pencarian ke kiri dan dua arah, banyak kriteria, serta kesalahan umum, ditambah perbandingan dengan VLOOKUP.

Automatically translated with AI from the original post. Read the English original

INDEX MATCH di Excel adalah rumus pencarian yang dibangun dari dua fungsi: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH menemukan letak nilai Anda dalam sebuah daftar, dan INDEX mengembalikan item dari posisi yang sama di daftar lain. Artikel ini ditujukan untuk pengguna Excel yang menulis rumus VLOOKUP dan mulai menemui batasannya, atau yang mewarisi buku kerja berisi banyak INDEX MATCH dan ingin memahaminya. Anda akan melihat setiap fungsi secara terpisah, cara keduanya bekerja sama, cara mencari ke kiri, cara melakukan pencarian dua arah, cara menggunakan dua kriteria, dan arti setiap kesalahan.

Poin Penting

  • INDEX MATCH adalah dua fungsi dalam satu rumus: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH menemukan posisi, dan INDEX mengembalikan item pada posisi tersebut.
  • Gunakan 0 sebagai argumen ketiga MATCH untuk kecocokan persis. Jika dibiarkan pada nilai default 1, MATCH mengasumsikan data sudah diurutkan secara menaik.
  • Karena rentang pencarian dan rentang hasil terpisah, INDEX MATCH dapat mencari ke kiri dari kolom pencarian, yang tidak dapat dilakukan VLOOKUP.
  • Untuk dua kondisi, Anda dapat menggabungkan kolom di kolom bantu atau menggunakan rumus yang mengalikan dua perbandingan di dalam MATCH.
  • Bungkus rumus dengan IFNA untuk mengganti #N/A dengan pesan, sementara kesalahan lain tetap muncul sehingga kesalahan sebenarnya tidak tersembunyi.

INDEX MATCH banyak ditemukan di buku kerja lama, karena sudah berfungsi di setiap versi Excel jauh sebelum XLOOKUP ada. Rumus ini masih berguna hingga sekarang, dan itulah alasan utama untuk mempelajarinya. Rincian di bawah ini bersumber dari halaman Microsoft tentang MATCH, INDEX, dan VLOOKUP.

Apa yang dilakukan INDEX

INDEX mengembalikan nilai dari suatu posisi di dalam sebuah rentang. Halaman Microsoft menuliskan bentuk array-nya sebagai INDEX(array, row_num, [column_num]).

  • array adalah rentang tempat nilai diambil.
  • row_num adalah baris di dalam rentang tersebut.
  • column_num adalah kolom di dalam rentang tersebut. Jika rentang hanya memiliki satu kolom, argumen ini boleh dihilangkan.

Angka dihitung dari sel kiri atas rentang, bukan dari lembar kerja. Contoh Microsoft menggunakan blok dengan Apples dan Bananas di kolom pertama serta Lemons dan Pears di kolom kedua. =INDEX(A2:B3,2,2) mengembalikan Pears, dan =INDEX(A2:B3,2,1) mengembalikan Bananas.

Ada dua aturan dari halaman yang sama yang layak diingat. Jika row_num atau column_num mengarah ke luar rentang, INDEX mengembalikan kesalahan #REF!. Jika row_num atau column_num diatur ke 0, INDEX mengembalikan seluruh kolom atau baris. Di Microsoft 365 saat ini, Anda cukup menekan Enter seperti biasa. Di versi lama, Anda memasukkannya sebagai rumus array dengan Ctrl+Shift+Enter.

Sendirian, INDEX memerlukan posisi yang sudah Anda ketahui. Di sinilah MATCH berperan.

Apa yang dilakukan MATCH

MATCH mencari dalam sebuah rentang dan mengembalikan posisi. Microsoft menuliskan sintaksisnya sebagai MATCH(lookup_value, lookup_array, [match_type]). Fungsi ini tidak mengembalikan item itu sendiri, hanya posisi relatifnya dalam rentang.

Argumen ketiga menentukan cara pencocokannya:

  • 0 menemukan kecocokan persis pertama. Rentang dapat berada dalam urutan apa pun. Ini adalah opsi yang paling sering Anda gunakan.
  • 1, yang juga menjadi nilai default jika argumen ini dihilangkan, menemukan nilai terbesar yang kurang dari atau sama dengan nilai pencarian. Rentang harus diurutkan secara menaik.
  • -1 menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian. Rentang harus diurutkan secara menurun.

Contoh dari Microsoft menggunakan nilai 25, 38, 40 dan 41 di B2:B5. =MATCH(39,B2:B5,1) mengembalikan 2, yaitu posisi 38, nilai terdekat yang lebih kecil. =MATCH(41,B2:B5,0) mengembalikan 4. Dan =MATCH(40,B2:B5,-1) mengembalikan #N/A, karena nilai-nilai tersebut tidak diurutkan secara menurun.

Ada beberapa perilaku lain dari halaman tersebut: pencocokan teks tidak membedakan huruf besar dan kecil, sehingga "apple" dan "APPLE" dianggap sama. Dengan tipe pencocokan 0 dan nilai pencarian berupa teks, ? cocok dengan satu karakter apa pun dan * cocok dengan rangkaian karakter apa pun, sedangkan tanda tilde sebelum salah satunya digunakan untuk mencari karakter literal tersebut. Jika MATCH tidak menemukan apa pun, fungsi ini mengembalikan #N/A.

Nilai default 1 sering menjebak banyak orang. Jika Anda lupa menulis argumen ketiga, MATCH diam-diam menganggap data sudah terurut dan dapat mengembalikan posisi yang salah tanpa menampilkan error. Pada hampir setiap pencarian ID, nama, atau kode, ketikkan 0.

Gabungkan semuanya

Polanya selalu sama: MATCH ditempatkan di dalam INDEX dan memberikan nomor baris.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Bayangkan sebuah lembar kerja dengan ID karyawan di A2:A100, nama di B2:B100, dan departemen di C2:C100. Anda mengetikkan ID di F2 dan ingin mengetahui departemennya.

  1. Klik sel tempat hasilnya akan ditampilkan, misalnya G2.
  2. Ketikkan =INDEX(C2:C100, untuk menentukan dari mana jawaban diambil.
  3. Tambahkan MATCH(F2,A2:A100,0)) untuk menentukan di mana ID dicari.
  4. Tekan Enter.

Rumus lengkapnya adalah =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH menemukan F2 di A2:A100 dan menghasilkan, misalnya, 14. INDEX kemudian mengembalikan item ke-14 di C2:C100.

Pastikan rentang memiliki panjang dan baris awal yang sama. Jika MATCH mencari di A2:A100 sementara INDEX membaca C3:C101, posisinya akan menunjuk ke baris yang salah dan hasilnya keliru tanpa peringatan. Gunakan baris awal dan akhir yang sama untuk keduanya, dan kunci dengan tanda dolar, $A$2:$A$100, jika Anda berencana menyalin rumus ke bawah.

Mencari ke kiri, dan perbedaannya dengan VLOOKUP

Halaman VLOOKUP dari Microsoft menuliskan sintaksnya sebagai VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Halaman tersebut menyebutkan bahwa nilai pencarian harus berada di kolom pertama rentang, dan bahwa jika range_lookup dihilangkan, hasilnya adalah kecocokan perkiraan yang mengasumsikan data terurut. Halaman itu juga menyarankan untuk mencoba XLOOKUP.

Berikut tugas yang sama dengan dua cara, dengan ID di kolom A dan nama di kolom B:

  • =VLOOKUP(F2,A2:B100,2,FALSE)
  • =INDEX(B2:B100,MATCH(F2,A2:A100,0))

INDEX MATCH memiliki dua keunggulan nyata. Pertama, ia dapat mencari ke kiri. Jika nama berada di kolom B dan Anda ingin mengambil kembali ID dari kolom A, cukup tukar rentangnya: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP tidak bisa melakukannya, karena nilai pencariannya harus berada di kolom pertama. Kedua, ia langsung menunjuk ke kolom hasil, alih-alih menghitung. Pada VLOOKUP, angka 2 dalam rumus berarti "kolom kedua dari rentang". Jika Anda menyisipkan kolom di dalam rentang, angka tersebut akan menunjuk ke kolom lain. Dengan INDEX MATCH, referensinya menyesuaikan seperti referensi lainnya.

Anda mungkin juga pernah membaca bahwa INDEX MATCH lebih cepat. Halaman Microsoft tidak menyatakan hal tersebut, dan perbedaannya hanya terasa pada buku kerja yang sangat besar, jadi jangan memilihnya demi kecepatan. Pilihlah karena fleksibilitasnya.

Pencarian dua arah

Pencarian dua arah menemukan nilai di titik pertemuan antara baris dan kolom, misalnya penjualan satu produk pada satu bulan. Gunakan dua fungsi MATCH, satu untuk baris dan satu untuk kolom:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Di sini B2:M20 berisi angka, A2:A20 berisi nama produk, B1:M1 berisi judul bulan, dan P1 serta Q1 berisi produk dan bulan yang Anda inginkan. MATCH pertama menghasilkan baris, MATCH kedua menghasilkan kolom, dan INDEX mengembalikan angka pada titik perpotongannya. Perhatikan bahwa rentang hasil dimulai di B2, bukan A1, agar posisinya sejajar dengan rentang label: kedua rentang MATCH juga dimulai pada baris atau kolom yang sama dengan angkanya.

Mencocokkan dengan dua kriteria

MATCH hanya menerima satu nilai pencarian. Jika sebuah catatan hanya dapat diidentifikasi dengan dua field, seperti kategori dan status, Anda memiliki dua pilihan.

Kolom bantu. Tambahkan kolom yang menggabungkan kedua field, misalnya =A2&"|"&B2 di D2, lalu salin ke bawah. Setelah itu, cari teks gabungan tersebut:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

Pemisah itu penting. Tanpa pemisah, "AB" ditambah "C" dan "A" ditambah "BC" sama-sama menjadi "ABC". Kolom bantu mudah dibaca dan diperiksa, serta berfungsi di semua versi.

Rumus array. Tanpa kolom bantu, Anda dapat mengalikan dua perbandingan:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Setiap perbandingan menghasilkan TRUE atau FALSE. Perkalian mengubahnya menjadi 1 hanya jika keduanya benar, dan MATCH mencari 1 pertama. Cara ini tidak tercantum di halaman INDEX dan MATCH milik Microsoft, jadi uji pada rentang kecil terlebih dahulu. Di versi Excel yang lebih lama, Anda mungkin perlu memasukkannya dengan Ctrl+Shift+Enter, sebagaimana Microsoft jelaskan untuk hasil array dari INDEX. Di Microsoft 365 saat ini, cukup tekan Enter.

Wildcard dan nilai terdekat

Dengan tipe pencocokan 0 dan teks, MATCH menerima wildcard. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) mengembalikan item di samping entri pertama yang diawali "Jan". Ini berguna untuk kode parsial. Buat pola Anda cukup spesifik, karena MATCH berhenti pada kecocokan pertama.

Untuk tingkatan seperti kelompok pajak atau pita komisi, gunakan tipe pencocokan default 1 secara sengaja, dengan ambang batas yang diurutkan secara menaik. Letakkan 0, 1000, dan 5000 di A2:A4 serta tarifnya di B2:B4, lalu =INDEX(B2:B4,MATCH(E2,A2:A4,1)) mengembalikan tarif dari ambang batas terbesar yang tidak melebihi E2. Ini salah satu dari sedikit kasus ketika default berbasis data terurut justru merupakan pilihan yang tepat.

Memperbaiki kesalahan

  • #N/A. MATCH tidak menemukan apa pun. Periksa salah ketik, spasi di akhir, dan angka yang tersimpan sebagai teks, karena "100" dan 100 berbeda. Pastikan tipe pencocokan adalah 0, dan bahwa tipe pencocokan untuk data terurut (1 atau -1) memang benar-benar didasarkan pada data yang terurut.
  • #REF!. INDEX diminta mengambil baris atau kolom di luar rentang. Ini juga terjadi saat Anda menghapus baris atau kolom yang digunakan rumus. Pastikan rentang MATCH dan rentang INDEX memiliki ukuran yang sama.
  • Jawaban salah tanpa pesan kesalahan. Biasanya karena angka 0 hilang pada MATCH, atau rentang yang dimulai pada baris yang berbeda.
  • Jawaban salah setelah disalin ke bawah. Rentang berpindah karena tidak dikunci. Tambahkan $ pada rentang tersebut.

Untuk mengganti kesalahan dengan pesan, fungsi IFNA dari Microsoft mengembalikan nilai yang Anda pilih ketika rumus menghasilkan #N/A, dan meneruskan kesalahan lain tanpa perubahan:

Untuk pencarian, utamakan IFNA daripada IFERROR. IFERROR menyembunyikan setiap kesalahan, termasuk #REF! akibat rentang yang rusak, sehingga kesalahan sungguhan tampak seperti "Not found" biasa. IFNA hanya menangani kasus yang memang Anda antisipasi. Microsoft mencantumkan IFNA untuk Excel 2016 dan yang lebih baru.

Lebih baik menggunakan IFNA daripada IFERROR untuk pencarian. IFERROR menyembunyikan semua kesalahan, termasuk #REF! yang disebabkan oleh rentang yang rusak, sehingga kesalahan sebenarnya tampak seperti "Not found" biasa. IFNA hanya menangani kasus yang memang Anda perkirakan. Halaman Microsoft mencantumkan Excel 2016 sebagai versi terlama yang didukung.

INDEX MATCH atau XLOOKUP

Jika semua orang yang membuka file Anda menggunakan Microsoft 365 terkini, XLOOKUP melakukan pekerjaan yang sama dengan satu fungsi dan pesan not-found bawaan. Kami membahasnya dalam panduan XLOOKUP di Excel kami. Namun, halaman XLOOKUP dari Microsoft tidak menjelaskan dengan jelas untuk Excel 2016 dan 2019, dan tidak mencantumkan Excel untuk web. INDEX MATCH berfungsi di semua versi tersebut. Jadi pilihannya bersifat praktis: gunakan XLOOKUP di file untuk versi terkini, dan INDEX MATCH ketika file dibagikan kepada orang yang memakai versi lama atau ketika Anda memelihara buku kerja yang sudah menggunakannya.

Kesalahan umum

  • Melewatkan 0 pada MATCH dan mengandalkan default.
  • Menggunakan rentang dengan ukuran atau baris awal yang berbeda untuk INDEX dan MATCH.
  • Lupa mengunci rentang dengan $ sebelum menyalin rumus ke bawah.
  • Menggabungkan kriteria tanpa pemisah di kolom bantu.
  • Membungkus semuanya dengan IFERROR, sehingga rumus yang rusak tampak seperti data yang hilang.
  • Anda mengharapkan INDEX MATCH mengembalikan semua kecocokan. Padahal, fungsi ini hanya mengembalikan kecocokan pertama.

Topik terkait untuk dijelajahi

  • XLOOKUP. Fungsi pencarian yang lebih baru, dengan arah pencarian dan argumen untuk kondisi tidak ditemukan yang sudah tersedia di dalamnya.
  • Beberapa hasil sekaligus. INDEX MATCH hanya mengembalikan kecocokan pertama. Fungsi FILTER adalah alat untuk mengembalikan setiap kecocokan di versi yang mendukungnya.
  • Tabel dan rentang bernama. Referensi terstruktur dan nama membuat pencarian lebih mudah dibaca dan memungkinkan rentang ikut bertambah seiring data Anda.
  • Performa pada lembar kerja besar. Gunakan rentang yang dibatasi alih-alih seluruh kolom, dan jaga agar rentang tetap ringkas.
  • Pencarian yang peka huruf besar-kecil. MATCH mengabaikan huruf besar-kecil, sehingga pencarian yang peka terhadap huruf memerlukan pendekatan lain. Uji setiap solusi alternatif sebelum Anda mengandalkannya.

Langkah berikutnya

Tulis ulang salah satu rumus VLOOKUP Anda menjadi INDEX MATCH, lalu bandingkan hasilnya pada beberapa nilai yang sudah Anda ketahui. Setelah itu, coba rumus yang sama dengan pencarian ke kiri. Setelah Anda melakukan keduanya, Anda akan tahu kapan INDEX MATCH di Excel menjadi alat yang lebih tepat untuk pekerjaan tersebut.

Sumber

FAQ

Bagaimana cara kerja INDEX MATCH di Excel?

MATCH mencari nilai pencarian Anda dalam sebuah rentang dan mengembalikan posisinya, misalnya 5 untuk item kelima. INDEX kemudian menggunakan angka tersebut untuk mengembalikan item pada posisi yang sama di rentang lain. Jika digabungkan, =INDEX(C2:C100,MATCH(F2,A2:A100,0)) mencari F2 di kolom A dan mengembalikan nilai dari kolom C.

Apakah INDEX MATCH lebih baik daripada VLOOKUP?

INDEX MATCH lebih fleksibel. VLOOKUP mengharuskan nilai pencarian berada di kolom pertama rentangnya dan menghitung kolom berdasarkan nomor, sedangkan INDEX MATCH menggunakan dua rentang terpisah, sehingga dapat mencari ke kiri dan tidak terpengaruh oleh penyisipan kolom. Untuk pencarian sederhana ke kanan, keduanya sama-sama bisa dipakai. XLOOKUP merupakan opsi ketiga di versi yang lebih baru.

Mengapa INDEX MATCH mengembalikan #N/A?

MATCH mengembalikan #N/A ketika tidak menemukan kecocokan. Periksa salah ketik, spasi berlebih, angka yang tersimpan sebagai teks, dan jenis kecocokannya. Gunakan 0 untuk kecocokan persis. Dengan nilai default 1, rentang pencarian harus diurutkan secara menaik, dan dengan -1 harus diurutkan secara menurun.

Entities in this article

Topik artikel ini:

Microsoft Excel – Alat analitik dan BI dari Microsoft

Related articles

Artikel lain tentang Microsoft Excel:

RSS