Apa itu INDEX MATCH Di Microsoft Excel?

Apa itu INDEX MATCH?

INDEX MATCH adalah cara cerdas untuk melakukan pencarian dua arah di Excel dengan menggabungkan kekuatan fungsi INDEX dan MATCH. Ini digunakan sebagai solusi untuk keterbatasan VLOOKUP, dan menawarkan fleksibilitas luar biasa setelah Anda memahami cara kerjanya.

Pertama, mari kita lihat apa yang dimaksud dengan VLOOKUP untuk dipecahkan, dan di mana kekurangannya.


Cara kerja VLOOKUP

Contoh VLOOKUP

Sel F2 hingga G5 bertindak seperti alat kueri sederhana untuk mengekstrak informasi dari kumpulan data di sel A2 hingga D9. VLOOKUP menggunakan Nomor Produk yang dimasukkan secara manual di G2 untuk mengembalikan setiap nilai masing-masing, dan argumen terakhir (FALSE) dalam sintaks VLOOKUP memastikan bahwa Excel mencari kecocokan persis antara nomor produk dan kolom pertama dalam kumpulan data sumber.

Ini bagus, tetapi ada batasan tertentu dalam VLOOKUP.

Keterbatasan VLOOKUP

    1. Dengan VLOOKUP, nilai pencarian harus selalu berada di kolom pertama dari array pencarian. VLOOKUP tidak dapat mengembalikan nilai yang berada di sebelah kiri nilai pencarian dalam larik sumber. Kedengarannya sederhana, tetapi itu benar, dan itu menjadi masalah yang sangat besar dengan data yang tidak dapat kita ubah atau atur ulang. Misalnya, jika kueri di atas dirancang bagi pengguna untuk memasukkan Jenis Kertas, VLOOKUP tidak akan dapat mengembalikan Nomor Produk dengan tata letak kumpulan data saat ini.
    1. Ketika perkiraan kecocokan diterima, (argumen terakhir dihilangkan atau TRUE), nilai pencarian dalam data sumber harus diurutkan dalam urutan menaik. Daftar yang diurutkan dalam urutan menurun akan mengembalikan hasil yang salah. Sekali lagi, Anda mungkin tidak memiliki kendali sebanyak ini atas data dalam larik pencarian Anda.

Jika Anda menggunakan VLOOKUP secara teratur, Anda akan mengalami masalah ini dan mungkin baru saja memutuskan untuk menggunakannya. Mempelajari cara menggunakan INDEX MATCH adalah solusi yang bagus.

Mari kita uraikan kedua fungsi tersebut satu per satu.

Cara menggunakan fungsi MATCH

Fungsi MATCH digunakan untuk menentukan nomor posisi nilai yang diketahui dalam rentang sel. Fungsi MATCH memiliki tiga kemungkinan argumen, dengan sintaks berikut:

=MATCH(lookup_value, lookup_array, [match_type])
    • Nilai lookup adalah nilai yang diketahui yang akan Anda gunakan untuk melakukan pencarian, atau pencarian. Ini bisa berupa nilai atau referensi sel.
    • Cari_array adalah rentang sel tempat nilai pencarian akan ditemukan.
    • Match_type adalah pengaturan yang memberi tahu Excel apakah Anda akan menerima hampir cocok jika nilai_pencarian tidak ditemukan dalam larik pencarian.
        • Jenis pencocokan 0 adalah untuk pencocokan tepat.
        • Jenis pertandingan 1 menerima hampir-cocok, bulat turun ke nilai berikutnya yang tersedia.
        • Jenis pertandingan -1 menerima putaran mendekati pertandingan ke atas ke nilai berikutnya yang tersedia.
        • Match_type adalah opsional. Jika dihilangkan, tipe kecocokan 1 diasumsikan.
Baca Juga:  Inilah Fungsi PMT di Microsoft Excel Yang Harus Diketahui!

Misalnya, jika kita ingin mengetahui nomor posisi kata “matte” dalam rentang B2 hingga B9 di bawah ini.

=MATCH(“matte”,B2:B9,0)

Contoh PERTANDINGAN

Perhatikan bahwa dalam contoh di atas, nilai “matte” diketik langsung ke dalam rumus di antara tanda kutip ganda. Jika nilai pencarian adalah referensi sel, tidak ada tanda kutip ganda yang digunakan.

Perhatikan juga bahwa fungsi MATCH tidak peka huruf besar/kecil.

Cara menggunakan fungsi INDEX

Itu Fungsi INDEX mengembalikan nilai atau referensi sel dari dalam tabel atau rentang. Fungsi INDEX memiliki dua format – format array dan format referensi. Format array digunakan ketika kita ingin mengembalikan nilai yang ditemukan di sel hasil. Format referensi digunakan ketika kita ingin Excel mengembalikan referensi sel dari sel hasil (misalnya D17). Kami akan fokus pada format array, karena ini adalah format yang perlu Anda pelajari untuk kombo INDEX/MATCH.

Sintaks dari fungsi array INDEX adalah

=INDEX(array, row_num, [column_num])
    • Himpunan mengacu pada rentang atau larik yang berisi data yang akan diindeks. Argumen ini diperlukan.
    • Baris_num adalah nomor baris tempat sel yang berisi nilai kembalian dapat ditemukan. Row_num dapat dihilangkan jika array hanya terdiri dari satu baris. Jika baris_num dihilangkan, kolom_num diperlukan.
    • kolom_num adalah nomor kolom tempat sel yang berisi nilai kembalian dapat ditemukan. Column_num adalah opsional, tetapi diperlukan jika baris_num dihilangkan.

Pada spreadsheet di bawah ini, kami ingin menampilkan nilai yang ada di baris ketiga, kolom kedua dari array dengan menggunakan fungsi INDEX.

=INDEX(A2:D9,3,2)

Contoh INDEKS

Item ketiga di kolom 2 diidentifikasi sebagai nilai “Matte” dan dikembalikan sebagai output di sel F2.

Menggabungkan fungsi INDEX dan MATCH

Seperti yang disebutkan sebelumnya, rumus INDEX dan MATCH, ketika disarangkan, dapat melakukan pencarian yang menyelesaikan apa yang dilakukan VLOOKUP dan banyak lagi. Menyarangkan rumus berarti menggunakan satu seluruh rumus sebagai argumen dari fungsi lain.

Baca Juga:  Cara Menghapus Duplikat di Excel

Kami dapat menerapkannya pada kueri sederhana kami yang mengekstrak nilai dari kumpulan data menggunakan Nomor Produk sebagai input, atau nilai pencarian.

Contoh PERTANDINGAN INDEKS

Kami akan menggunakan MATCH untuk menentukan di mana dalam daftar nomor produk jatuh (nomor posisi), dan kami akan menggunakan INDEX untuk mengembalikan nilai dari kolom yang sesuai (Jenis Kertas, Harga, atau Stok,) dengan nomor kolomnya.

Ditampilkan secara terpisah, setiap fungsi akan terbaca sebagai berikut:

=MATCH(G2, A2:A9,0)

Ini melakukan pencarian untuk nilai di sel G2 dalam rentang A2 hingga A9, dan membutuhkan kecocokan yang sama persis. Ini mengembalikan nomor posisi dari nilai itu.

=INDEX(A2:D9, row_num, 2)

Ini mengembalikan nilai dari larik A2 ke D9 yang memotong nomor baris yang ditemukan dalam rumus MATCH, dan nomor kolom 2 (Jenis Kertas). Rumus MATCH akan digunakan sebagai baris_num argumen.

=INDEX(A2:D9,MATCH(G2,A2:A9,0),2)

Contoh PERTANDINGAN INDEKS 2Rumus pada G4 dan G5 adalah identik, kecuali untuk argumen terakhir, yaitu 3 dan 4 masing-masing, untuk mewakili setiap nomor kolom.

Agar adil, ini memang tampak cukup rumit ketika kami menganggap bahwa VLOOKUP sudah melakukan ini dengan cara yang jauh lebih mudah, dan hanya dengan empat argumen. Namun, pada awalnya, kami menunjukkan bahwa VLOOKUP tidak dapat mengembalikan nilai yang terletak di sebelah kiri nilai pencarian.

Jika kita mengubah nilai yang diketahui sehingga Jenis Kertas sekarang menjadi milik kita nilai lookup, VLOOKUP mengembalikan kesalahan #NA (Nilai Tidak Tersedia). Mari kita lihat bagaimana INDEX MATCH menangani ini.

=INDEX(A2:D9, MATCH(G2, B2:B9,0),1)

Contoh PERTANDINGAN INDEKS 3

Pencarian ke kiri atau kanan tidak menjadi masalah untuk INDEX MATCH, karena MATCH lookup_array berdiri sendiri, dan tidak terhubung ke array INDEX.

Dengan cara ini, INDEX MATCH juga dapat digunakan sebagai pengganti HLOOKUP, yang merupakan versi horizontal dari fungsi VLOOKUP.

Mari kita lihat aplikasi yang lebih canggih dari solusi INDEX MATCH.

Contoh lanjutan INDEX MATCH

Contoh PERTANDINGAN INDEKS 4Peserta ujian telah memasukkan ID Pelajar mereka pada kertas ujian mereka, yang kemudian diberi skor dan nilainya dimasukkan di kolom D. Kami ingin mencari ID Pelajar dalam larik A2 hingga C7, tetapi VLOOKUP tidak dapat melakukannya. Nama siswa ditampilkan di sebelah kiri nomor ID karena daftar disusun menurut abjad. Itu adalah masalah pertama kami.

Baca Juga:  Cara Menghitung Regresi Linear Sederhana Menggunakan Excel

Kami juga ingin menetapkan nilai huruf berdasarkan nilai ujian setiap siswa. Chart skor tes (F2 hingga G6) diurutkan dalam urutan menurun, yang merupakan hal lain yang tidak dapat ditangani oleh VLOOKUP.

Fungsi MATCH akan digunakan untuk menentukan nomor baris dari fungsi INDEX.

=MATCH(B11,$C$2:$C$7,0)

(Rentang C2 hingga C7 akan disalin, jadi kita dapat menggunakan $ untuk membuat referensi tetap. Pelajari lebih lanjut tentang Referensi Mutlak dan Campuran.)

Kami menggunakan jenis pencocokan 0 untuk memastikan bahwa hanya pencocokan tepat yang akan ditampilkan.

Contoh PERTANDINGAN INDEKS 5Fungsi MATCH menempatkan ID Pelajar 114125 di posisi 5 dari larik pencarian.

Sekarang kita hanya perlu menautkannya ke nama siswa yang sesuai.

=INDEX($A$2:$C$7,MATCH(B11,$C$2:$C$7,0),1)

(Rentang A2 hingga C7 akan disalin, jadi kita dapat menggunakan $ untuk membuat referensi tetap. Pelajari lebih lanjut tentang Referensi Mutlak dan Campuran.)

Rumus MATCH telah dimasukkan, atau bersarang, dalam fungsi INDEX sebagai cara untuk mengetahui baris apa yang akan dikembalikan di setiap instance. Kolom pertama dari array berisi nilai yang ingin kita kembalikan, jadi argumen terakhir dari fungsi INDEX (kolom_num), adalah 1.

Contoh PERTANDINGAN INDEKS 6Rumus ini sekarang dapat disalin ke baris yang tersisa di kolom C.

Menentukan nilai huruf untuk setiap siswa akan mengikuti pola yang sama, dengan satu pengecualian. Karena grafik penilaian hanya mencantumkan lima nilai, nilai ujian siswa mungkin tidak sama persis, oleh karena itu: match_type dalam rumus MATCH harus disesuaikan untuk menerima perkiraan kecocokan.

Tipe Array rumus MATCH match_type
Benar-benar cocok

Perkiraan kecocokan, ke nilai terdekat yang lebih kecil dari nilai pencarian

1

Perkiraan kecocokan, ke nilai terdekat yang lebih besar dari nilai pencarian

-1

INDEX MATCH contoh 7

Rumus MATCH dan INDEX kami akan berbunyi

    • COCOK(H11,$F$2:$F$6,-1)
    • INDEX($F$2:$G$6,rumus PERTANDINGAN bersarang,2)

Saat bersarang, rumus lengkapnya adalah:

=INDEX($F$2:$G$6,MATCH(D11,$F$2:$F$6,-1),2)

INDEX MATCH contoh 8

Jadi meskipun VLOOKUP dibuat untuk menyederhanakan tugas umum menemukan item dalam tabel, kami tidak dapat membuang INDEX MATCH untuk tugas yang lebih lanjut.

Inilah sesuatu yang mungkin ingin Anda ketahui – Excel telah meluncurkan XLOOKUP sebagai perbaikan untuk keluhan di atas tentang keterbatasan VLOOKUP. Kekurangannya adalah untuk saat ini hanya tersedia di Excel 365, jadi jika Anda memiliki versi yang lebih lama, Anda pasti ingin memahami dan menguasai INDEX MATCH.

Tinggalkan komentar