INDEX dan MATCH adalah alat paling populer di Microsoft Excel untuk melakukan pencarian tingkat lanjut. INDEX dan MATCH sangat fleksibel sehingga dapat digunakan untuk pencarian horizontal dan vertikal, pencarian 2 arah, pencarian kiri, pencarian peka huruf besar/kecil, dan bahkan pencarian berdasarkan beberapa kriteria tertentu. Untuk menggunakan Microsoft Excel dengan telaten, kamu bisa mempelajari penjelasan berikut ini :
Dibutuhkan ketelitian ekstra dalam hal ini, mulai INDEX, lalu MATCH, kemudian menggabungkan kedua fungsi tersebut untuk membuat pencarian dua arah yang dinamis.
Fungsi INDEKS
Fungsi INDEX di Excel sangat fleksibel dan kuat, dapat ditemukan di sejumlah besar rumus Excel, terutama rumus tingkat lanjut. Tapi apa sebenarnya yang INDEX lakukan? Singkatnya, INDEX mengambil nilai di lokasi tertentu dalam suatu rentang.
Misalnya, katakanlah Anda memiliki tabel planet di tata surya (lihat di bawah), dan Anda ingin mendapatkan nama planet ke-4, Mars, dengan rumus. Anda dapat menggunakan INDEX seperti ini:
= INDEKS ( B3:B11 , 4 )
Bagaimana jika Anda ingin mendapatkan diameter Mars dengan INDEX? Dalam hal ini, kami dapat menyediakan nomor baris dan nomor kolom, dan memberikan rentang yang lebih besar. Rumus INDEX di bawah ini menggunakan data lengkap di B3:D11, dengan nomor baris 4 dan nomor kolom 2:
= INDEKS ( B3:D11 , 4 , 2 )
Menggunakan INDEX untuk mendapatkan diameter planet ke-4
INDEX mengambil nilai pada baris 4, kolom 2. Untuk meringkas, INDEX mendapatkan nilai di lokasi tertentu dalam rentang sel berdasarkan posisi numerik. Bila rentangnya satu dimensi, Anda hanya perlu memberikan nomor baris. Bila rentangnya dua dimensi, Anda harus memberikan nomor baris dan kolom.
Pada titik ini, Anda mungkin berpikir “Jadi, Seberapa besar keterampilan yang benar-benar dikuasai pada posisi sesuatu dalam spreadsheet?”
Tepat sekali. Kami membutuhkan cara untuk menemukan posisi barang yang dicari.
Fungsi MATCH
Fungsi MATCH dirancang untuk satu tujuan: menemukan posisi item dalam rentang. Misalnya, kita bisa menggunakan MATCH untuk mendapatkan posisi kata “peach” dalam daftar buah-buahan seperti ini:
= COCOK ( “persik” , B3:B9 , 0 )
MATCH mengembalikan 3, karena “Peach” adalah item ke-3. MATCH tidak menggunakan huruf besar/kecil.
MATCH tidak peduli apakah rentang horizontal atau vertikal, seperti yang Anda lihat di bawah:
= COCOK ( “peach” , C4:I4 , 0 )
Hasil yang sama dengan rentang horizontal, MATCH mengembalikan 3.
Penggabungan INDEX dan MATCH
Sekarang setelah kita membahas dasar-dasar INDEX dan MATCH, bagaimana kita menggabungkan kedua fungsi dalam satu rumus? Perhatikan data di bawah ini, tabel yang menunjukkan daftar tenaga penjualan dan angka penjualan bulanan selama tiga bulan: Januari, Februari, dan Maret.
Katakanlah jika kita ingin menulis formula yang mengembalikan nomor penjualan bulan Februari untuk tenaga penjual tertentu. Dari pembahasan di atas, kita tahu bahwa kita dapat memberikan INDEX nomor baris dan kolom untuk mengambil nilai. Misalnya, untuk mengembalikan nomor penjualan Februari untuk Frantz, kami menyediakan rentang C3:E11 dengan baris 5 dan kolom 2:
= INDEX ( C3:E11 , 5 , 2 ) // mengembalikan $5194
Tapi kami jelas tidak ingin nomor hardcode. Sebagai gantinya, kami menginginkan pencarian dinamis .
Bagaimana kita akan melakukannya? Fungsi MATCH tentu saja. MATCH akan bekerja dengan sempurna untuk menemukan posisi yang kita butuhkan. Dengan langkah-langkah yang sesuai untuk kedua kolom. Inilah rumus yang direvisi, dengan fungsi MATCH berada di dalam INDEX sebagai ganti 5:
= INDEX ( C3:E11 , MATCH ( “Frantz” , B3:B11 , 0 ), 2 )
Mengambil satu langkah lebih jauh, kami akan menggunakan nilai dari H2 di MATCH:
= INDEKS ( C3:E11 , MATCH ( H2 , B3:B11 , 0 ), 2 )
MATCH menemukan “Frantz” dan mengembalikan 5 ke INDEX untuk baris.
Untuk meringkas:
- INDEX membutuhkan posisi numerik.
- MATCH menemukan posisi tersebut.
- MATCH bersarang di dalam INDEX.
Sekarang mari kita menangani nomor kolom.
Pencarian dua arah dengan INDEX dan MATCH
Di atas, kami menggunakan fungsi MATCH untuk menemukan nomor baris secara dinamis, tetapi kodenya sulit pada nomor kolom. Bagaimana kita bisa memembuat rumus menjadi dinamis sepenuhnya, sehingga kita dapat mengembalikan penjualan untuk setiap tenaga penjual tertentu pada bulan tertentu? Triknya adalah menggunakan MATCH dua kali – sekali untuk mendapatkan posisi baris, dan sekali untuk mendapatkan posisi kolom.
Dari contoh di atas, kita tahu MATCH berfungsi baik dengan array horizontal dan vertikal. Oleh karena itu, kita dapat dengan mudah menemukan posisi bulan tertentu dengan MATCH. Misalnya, rumus ini mengembalikan posisi bulan Maret, yaitu 3:
= MATCH ( “Mar” , C2:E2 , 0 ) // mengembalikan 3
Tapi tentu saja kita tidak ingin mengkode sulit nilai apapun , jadi mari perbarui lembar kerja untuk mengizinkan input nama bulan, dan gunakan MATCH untuk menemukan nomor kolom yang kita butuhkan. Layar di bawah ini menunjukkan hasilnya:
Pencarian dua arah yang sepenuhnya dinamis dengan INDEX dan MATCH.
= INDEKS ( C3:E11 , MATCH ( H2 , B3:B11 , 0 ), MATCH ( H3 , C2:E2 , 0 ))
Rumus MATCH pertama mengembalikan 5 ke INDEX sebagai nomor baris, rumus MATCH kedua mengembalikan 3 ke INDEX sebagai nomor kolom. Setelah MATCH berjalan, rumus disederhanakan menjadi:
= INDEKS ( C3:E11 , 5 , 3 )
Dan INDEX dengan benar mengembalikan $10.525, angka penjualan untuk Frantz di bulan Maret.
Artikel menarik lainnya : validasi data di excel
Pencarian kiri (Left Lookup)
Salah satu keuntungan utama INDEX dan MATCH dibandingkan fungsi VLOOKUP adalah kemampuan untuk melakukan “pencarian kiri”. Singkatnya dalam kolom ID berada di sebelah kanan nilai yang ingin Anda ambil, seperti yang terlihat pada contoh di bawah ini:
Pencarian peka huruf besar/kecil (Case-Sensitif Lookup)
Pada dasarnya fungsi MATCH tidak peka huruf besar-kecil. Namun, Anda menggunakan fungsi EXACT dengan INDEX dan MATCH untuk melakukan pencarian khusus huruf besar dan kecil, seperti yang ditunjukkan di bawah ini:
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Kecocokan terdekat (Closest Match)
Contoh lain yang menunjukkan fleksibilitas INDEX dan MATCH adalah masalah menemukan kecocokan terdekat . Pada contoh di bawah ini, kita menggunakan fungsi MIN bersama dengan fungsi ABS untuk membuat nilai pencarian dan larik pencarian di dalam fungsi MATCH. Pada dasarnya, kami menggunakan MATCH untuk menemukan perbedaan terkecil. Kemudian kami menggunakan INDEX untuk mengambil perjalanan terkait dari kolom B.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Artikel yang mungkin anda suka : aplikasi excel untuk smartphone
Pencarian beberapa kriteria
Salah satu masalah tersulit di Excel adalah pencarian berdasarkan beberapa kriteria. Dengan kata lain, pencarian yang cocok di lebih dari satu kolom secara bersamaan. Pada contoh di bawah ini, kami menggunakan INDEX dan MATCH di Excel serta logika boolean untuk mencocokkan pada 3 kolom: Item, Color, dan Size:
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Nah, gimana penjelasannya sangat lengkap bukan?. Semoga artikel ini bermanfaat bagi kalian semua.
