Kapan Menggunakan Pencocokan Indeks dan bukannya VLOOKUP di Excel

2019

Bagi Anda yang berpengalaman dalam Excel, Anda kemungkinan besar sangat akrab dengan fungsi VLOOKUP . Fungsi VLOOKUP digunakan untuk menemukan nilai dalam sel yang berbeda berdasarkan pada beberapa teks yang cocok dalam baris yang sama.

Jika Anda masih baru dalam fungsi VLOOKUP, Anda dapat melihat posting saya sebelumnya tentang cara menggunakan VLOOKUP di Excel.

Sekuat apa pun itu, VLOOKUP memiliki batasan tentang bagaimana tabel referensi yang cocok perlu disusun agar formula berfungsi.

Artikel ini akan menunjukkan kepada Anda batasan di mana VLOOKUP tidak dapat digunakan dan memperkenalkan fungsi lain di Excel yang disebut INDEX-MATCH yang dapat menyelesaikan masalah.

INDEX MATCH Contoh Excel

Menggunakan contoh Excel spreadsheet berikut, kami memiliki daftar nama pemilik mobil dan nama mobil. Dalam contoh ini, kami akan mencoba mengambil ID Mobil berdasarkan Model Mobil yang tercantum di bawah beberapa pemilik seperti yang ditunjukkan di bawah ini:

Pada lembar terpisah bernama CarType, kami memiliki basis data mobil sederhana dengan ID, Model Mobil, dan Warna .

Dengan pengaturan tabel ini, fungsi VLOOKUP hanya dapat berfungsi jika data yang ingin kami ambil terletak di kolom di sebelah kanan apa yang ingin kami cocokkan (bidang Model Mobil ).

Dengan kata lain, dengan struktur tabel ini, karena kami mencoba mencocokkannya berdasarkan Model Mobil, satu-satunya informasi yang bisa kami dapatkan adalah Warna (Bukan ID karena kolom ID terletak di sebelah kiri kolom Model Mobil .)

Ini karena dengan VLOOKUP, nilai pencarian harus muncul di kolom pertama dan kolom pencarian harus di sebelah kanan. Tidak satu pun dari kondisi tersebut dipenuhi dalam contoh kita.

Berita baiknya adalah, INDEX-MATCH akan dapat membantu kita dalam mencapai ini. Dalam praktiknya, ini sebenarnya menggabungkan dua fungsi Excel yang dapat bekerja secara individual: fungsi INDEX dan fungsi MATCH .

Namun, untuk tujuan artikel ini, kami hanya akan berbicara tentang kombinasi keduanya dengan tujuan mereplikasi fungsi VLOOKUP .

Rumusnya bisa agak panjang dan menakutkan pada awalnya. Namun, setelah Anda menggunakannya beberapa kali, Anda akan mempelajari sintaksisnya dengan hati.

Ini adalah rumus lengkap dalam contoh kita:

 = INDEX (CarType! $ A $ 2: $ A $ 5, MATCH (B4, CarType! $ B $ 2: $ B $ 5.0))

Berikut ini rincian untuk setiap bagian

= INDEX ( - The "=" menunjukkan awal formula dalam sel dan INDEX adalah bagian pertama dari fungsi Excel yang kami gunakan.

CarType! $ A $ 2: $ A $ 5 - kolom pada lembar CarType tempat data yang ingin kita ambil terkandung. Dalam contoh ini, ID masing-masing Model Mobil.

MATCH ( - Bagian kedua dari fungsi Excel yang kami gunakan.

B4 - Sel yang berisi teks pencarian yang kami gunakan ( Model Mobil ) .

CarType! $ B $ 2: $ B $ 5 - Kolom pada lembar CarType dengan data yang akan kami gunakan untuk mencocokkan dengan teks pencarian.

0)) - Untuk menunjukkan bahwa teks pencarian harus sama persis dengan teks di kolom yang cocok (yaitu CarType! $ B $ 2: $ B $ 5 ). Jika kecocokan yang tepat tidak ditemukan, rumus mengembalikan # N / A.

Catatan : ingat bracket penutup ganda di akhir fungsi ini "))" dan koma di antara argumen.

Secara pribadi saya telah pindah dari VLOOKUP dan sekarang menggunakan INDEX-MATCH karena mampu melakukan lebih dari VLOOKUP.

Fungsi INDEX-MATCH juga memiliki manfaat lain dibandingkan dengan VLOOKUP :

Perhitungan lebih cepat

Ketika kami bekerja dengan kumpulan data besar di mana penghitungannya sendiri dapat memakan waktu lama karena banyak fungsi VLOOKUP, Anda akan menemukan bahwa setelah Anda mengganti semua rumus itu dengan INDEX-MATCH, perhitungan keseluruhan akan dihitung lebih cepat.

Tidak Perlu Menghitung Kolom Relatif

Jika tabel referensi kami memiliki teks utama yang ingin kami cari di kolom C dan data yang perlu kami dapatkan ada di kolom AQ, kita perlu mengetahui / menghitung berapa kolom antara kolom C dan kolom AQ saat menggunakan VLOOKUP .

Dengan fungsi INDEX-MATCH, kita dapat langsung memilih kolom indeks (yaitu kolom AQ) di mana kita perlu mendapatkan data dan memilih kolom yang akan dicocokkan (yaitu kolom C).

Terlihat Lebih Rumit

VLOOKUP sangat umum saat ini, tetapi tidak banyak yang tahu tentang menggunakan fungsi INDEX-MATCH bersama-sama.

String yang lebih panjang dalam fungsi INDEX-MATCH membantu membuat Anda terlihat seperti seorang ahli dalam menangani fungsi-fungsi Excel yang kompleks dan canggih. Nikmati!