Meskipun fungsi VLOOKUP bagus untuk menemukan nilai di Excel, ia memiliki keterbatasan. Menggunakan kombinasi fungsi INDEX dan MATCH, Anda dapat mencari nilai di lokasi atau arah mana pun di spreadsheet Anda.
Fungsi INDEX mengembalikan nilai berdasarkan lokasi yang Anda masukkan dalam rumus sementara MATCH melakukan sebaliknya dan mengembalikan lokasi berdasarkan nilai yang Anda masukkan. Saat Anda menggabungkan fungsi-fungsi ini, Anda dapat menemukan nomor atau teks apa pun yang Anda butuhkan.
VLOOKUP Versus INDEX dan MATCH
Perbedaan antara fungsi ini dan VLOOKUP adalah VLOOKUP menemukan nilai dari kiri ke kanan. Oleh karena itu nama fungsinya; VLOOKUP melakukan pencarian vertikal.
Microsoft paling baik menjelaskan cara kerja VLOOKUP:
Ada batasan tertentu dalam menggunakan VLOOKUP—fungsi VLOOKUP hanya dapat mencari nilai dari kiri ke kanan. Artinya kolom yang berisi nilai yang Anda cari harus selalu berada di sebelah kiri kolom yang berisi nilai kembalian.
Microsoft bahkan mengatakan bahwa jika lembar Anda tidak diatur sedemikian rupa sehingga VLOOKUP dapat membantu Anda menemukan apa yang Anda butuhkan, Anda dapat menggunakan INDEX dan MATCH sebagai gantinya. Jadi mari kita lihat bagaimana menggunakan INDEX dan MATCH di Excel.
INDEX dan MATCH Fungsi Dasar
Untuk menggunakan fungsi-fungsi ini bersama-sama, penting untuk memahami tujuan dan strukturnya.
Sintaks untuk INDEX dalam Formulir Array adalah INDEX(array, row_number, column_number) dengan dua argumen pertama diperlukan dan opsional ketiga.
INDEX mencari posisi dan mengembalikan nilainya. Untuk menemukan nilai di baris keempat dalam rentang sel D2 hingga D8, Anda memasukkan rumus berikut:
=INDEX(D2:D8,4)
Hasilnya adalah 20.745 karena itu adalah nilai di posisi keempat rentang sel kita.
Untuk detail lebih lanjut tentang Array dan Formulir Referensi INDEX serta cara lain untuk menggunakan fungsi ini, lihat cara kami untuk INDEX di Excel.
Sintaks untuk MATCH adalah MATCH(value, array, match_type) dengan dua argumen pertama diperlukan dan opsional ketiga.
MATCH mencari nilai dan mengembalikan posisinya. Untuk menemukan nilai di sel G2 dalam rentang A2 hingga A8, Anda memasukkan rumus berikut:
=MATCH(G2,A2:A8)
Hasilnya adalah 4 karena nilai di sel G2 berada di posisi keempat dalam rentang sel kita.
Untuk lebih jelasnya tentang match_type argumen dan cara lain untuk menggunakan fungsi ini, lihat tutorial kami untuk MATCH di Excel.
TERKAIT: Cara Menemukan Posisi Nilai Dengan MATCH di Microsoft Excel
Cara Menggunakan INDEX dan MATCH di Excel
Sekarang setelah Anda mengetahui apa yang dilakukan setiap fungsi dan sintaksisnya, inilah saatnya untuk menjalankan duo dinamis ini. Di bawah ini, kami akan menggunakan data yang sama seperti di atas untuk INDEX dan MATCH satu per satu.
Anda memasukkan rumus untuk fungsi MATCH di dalam rumus fungsi INDEX sebagai ganti posisi yang akan dicari.
Untuk menemukan jumlah (penjualan) berdasarkan ID lokasi, Anda akan menggunakan rumus ini:
=INDEX(D2:D8,MATCH(G2,A2:A8))
Hasilnya adalah 20.745. MATCH mencari nilai di sel G2 dalam rentang A2 hingga A8 dan meneruskannya ke INDEX yang mencari di sel D2 hingga D8 untuk hasilnya.
Mari kita lihat contoh lain. Kami ingin tahu kota mana yang memiliki penjualan yang sesuai dengan jumlah tertentu. Menggunakan lembar kami, Anda memasukkan rumus ini:
=INDEX(B2:B8,MATCH(G5,D2:D8))
Hasilnya adalah Houston. MATCH mencari nilai di sel G5 dalam rentang D2 hingga D8 dan meneruskannya ke INDEX yang mencari di sel B2 hingga B8 untuk hasilnya.
Berikut adalah contoh menggunakan nilai aktual alih-alih referensi sel. Kami akan menemukan nilai (penjualan) untuk kota tertentu dengan rumus ini:
=INDEX(D2:D8,MATCH("Houston",B2:B8))
Dalam rumus MATCH, kami mengganti referensi sel yang berisi nilai pencarian dengan nilai pencarian aktual “Houston” dari B2 ke B8 yang memberi kami hasil 20.745 dari D2 ke D8.
Catatan: Pastikan saat Anda menggunakan nilai aktual untuk mencari, alih-alih referensi sel, Anda menyertakannya dalam tanda kutip seperti yang ditunjukkan di sini.
Untuk mendapatkan hasil yang sama dengan menggunakan ID lokasi alih-alih kota, kami hanya akan mengubah rumus di sini:
=INDEX(D2:D8,MATCH("2B",A2:A8))
Di sini kita mengubah rumus MATCH untuk mencari “2B” dalam rentang sel A2 hingga A8 dan menetapkan hasilnya ke INDEX yang mengembalikan 20.745.
Fungsi Excel dasar seperti yang membantu Anda menambahkan angka ke sel atau memasukkan tanggal saat ini tentu sangat membantu. Tetapi ketika Anda mulai menambahkan lebih banyak data dan memajukan entri data atau kebutuhan analisis Anda, fungsi pencarian seperti INDEX dan MATCH di Excel bisa berguna.