Menggabungkan INDEX dan MATCH

Sekarang Anda mengetahui dua bagian dari pencarian. MATCH menemukan di mana suatu nilai berada, sedangkan INDEX mengembalikan nilai pada suatu posisi.

Gabungkan keduanya dan Anda mendapatkan pencarian lengkap: MATCH menemukan baris, lalu INDEX mengambil data dari baris tersebut pada kolom mana pun yang Anda pilih.

Polanya sederhana setelah Anda memahaminya: letakkan MATCH di dalam INDEX, pada posisi yang biasanya digunakan untuk nomor baris.

Berikut bentuk yang akan Anda gunakan berulang kali:

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

Bacalah dari dalam ke luar. MATCH dijalankan terlebih dahulu dan mengembalikan nomor posisi. Nomor tersebut kemudian menjadi nomor_baris untuk INDEX, yang mengembalikan nilai dari rentang hasil Anda.

Rentang hasil dan rentang pencarian biasanya memiliki jumlah baris yang sama, sehingga posisi pada satu rentang sejajar dengan posisi pada rentang lainnya.

Bayangkan sebuah tabel dengan kolom A berisi nama produk dan kolom C berisi harga. Anda ingin mengetahui harga "Ceri".

Pertama, MATCH menemukan Ceri: =MATCH("Cherry", A2:A20, 0) mengembalikan, misalnya, 3.

Kemudian INDEX menggunakan angka 3 tersebut: =INDEX(C2:C20, 3) mengembalikan harga pada baris ke-3 kolom C.

Susun keduanya dalam satu rumus dan Anda mendapatkannya sekaligus: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

Menulis "Ceri" secara langsung memang baik untuk belajar, tetapi rumus nyata biasanya merujuk ke sebuah sel. Masukkan istilah pencarian di E1 dan gunakan referensi ke sel tersebut.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Sekarang, produk apa pun yang Anda ketikkan di E1 akan langsung menampilkan harganya. Ketik Pisang dan Anda mendapatkan harga Pisang; ketik Kurma dan jawabannya diperbarui.

Satu rumus berubah menjadi alat pencarian yang dapat digunakan kembali dan sepenuhnya dikendalikan oleh sel masukan.

Inilah trik yang membuat INDEX-MATCH istimewa. Kolom pencarian dan kolom hasil tidak saling bergantung, sehingga nilai yang dikembalikan dapat berada di kiri nilai yang Anda cari.

Misalkan harga berada di kolom A dan nama produk berada di kolom C. Untuk menemukan harga produk berdasarkan namanya, tuliskan =INDEX(A2:A20, MATCH(E1, C2:C20, 0)).

Anda mencari di kolom C, tetapi mengambil hasil dari kolom A. VLOOKUP tidak dapat melakukan ini tanpa bantuan.

Rentang hasil menentukan apa yang Anda dapatkan. Dengan mencari berdasarkan kunci yang sama, Anda dapat mengambil kolom mana pun yang diinginkan hanya dengan mengubah rentang INDEX.

Temukan alamat surel pelanggan: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Untuk menemukan kota pelanggan yang sama: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

Bagian MATCH tetap sama; hanya rentang INDEX yang diubah untuk memilih hasil yang berbeda.

Anda juga dapat memberikan nomor kolom kepada INDEX, yang diperoleh melalui MATCH kedua. Dengan begitu, Anda dapat menentukan nilai pada perpotongan baris dan kolom.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

MATCH pertama menemukan baris berdasarkan label di A, sedangkan MATCH kedua menemukan kolom berdasarkan tajuk di baris 1. INDEX mengembalikan sel tempat keduanya berpotongan. Pola lanjutan ini akan dibahas lebih mendalam nanti.

Agar posisinya sejajar, rentang pencarian dan rentang hasil Anda harus dimulai pada baris yang sama dan memiliki jumlah baris yang sama.

Jika MATCH mencari di A2:A20 (19 baris), tetapi INDEX mengambil hasil dari C2:C19 (18 baris), posisi akan bergeser dan Anda mendapatkan jawaban yang salah.

Kebiasaan yang dapat diandalkan: gunakan rentang baris yang benar-benar sama untuk keduanya, seperti A2:A20 dan C2:C20. Referensi seluruh kolom seperti A:A dan C:C juga otomatis tetap sejajar.

Jika MATCH tidak dapat menemukan nilai pencarian, fungsi tersebut mengembalikan #N/A dan seluruh INDEX-MATCH menampilkan kesalahan itu. Bungkus rumus dengan IFNA untuk mendapatkan nilai pengganti yang rapi.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Sekarang, produk yang tidak ditemukan menampilkan teks "Tidak ditemukan", bukan kesalahan yang mengkhawatirkan. IFERROR juga dapat digunakan, tetapi IFNA hanya menangani kasus tidak ditemukan dan membiarkan kesalahan lain muncul.

Gabungkan semuanya. Anda memiliki tabel karyawan: ID di kolom A, nama di B, departemen di C, dan gaji di D. Pengguna mengetikkan ID ke G1.

Untuk menampilkan departemen karyawan tersebut: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

Untuk menampilkan gajinya, gantilah rentang INDEX menjadi D2:D200. Logika pencarian tidak pernah berubah; yang berubah hanya kolom tempat Anda mengambil data. Inilah fungsi andalan untuk pencarian dinamis sehari-hari.

Saat sebuah rumus terlihat mengintimidasi, evaluasilah seperti yang dilakukan lembar bentang, mulai dari fungsi yang paling dalam lalu bergerak ke luar.

Untuk =INDEX(C2:C20, MATCH(E1, A2:A20, 0)): pertama-tama baca MATCH(E1, A2:A20, 0), bayangkan fungsi itu mengembalikan angka seperti 5, lalu secara mental gantilah dengan =INDEX(C2:C20, 5).

Tiba-tiba rumus tersebut hanya berarti "mengembalikan harga ke-5". Kebiasaan ini memudahkan penelusuran kesalahan pada setiap pencarian bertingkat.

Anda menggabungkan kedua fungsi tersebut menjadi pencarian yang fleksibel:

• Pola: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH menemukan posisi baris; INDEX mengembalikan nilai pada posisi tersebut
• Kolom pencarian dan kolom hasil tidak saling bergantung, sehingga Anda dapat mencari ke kiri semudah ke kanan
• Pastikan kedua rentang memiliki jumlah baris yang sama, dan gunakan IFNA untuk menangani kesalahan dengan rapi

Selanjutnya, lihat alasan tepatnya pendekatan ini sering mengungguli VLOOKUP.