Indeks Cocok dengan Beberapa Baris dan Kolom Kriteria
Kita semua menggunakan VLOOKUP hari demi hari untuk mengambil data dan juga menyadari fakta bahwa VLOOKUP dapat mengambil data dari kiri ke kanan, jadi nilai pencarian harus selalu berada di sisi kiri kolom hasil. Namun, kami memiliki beberapa alternatif yang dapat digunakan sebagai alternatif fungsi VLOOKUP di excel. Dengan teknologi canggih, kita dapat menggunakan rumus INDEX + MATCH ini untuk mencocokkan beberapa kriteria untuk baris dan kolom. Jadi artikel khusus ini akan memandu Anda secara mendetail tentang teknik ini.
Bagaimana Menggunakan Rumus INDEX + MATCH untuk Mencocokkan Beberapa Kriteria?
Di sini kami menjelaskan cara menggunakan rumus indeks + kecocokan untuk mencocokkan beberapa kriteria untuk baris dan kolom dengan contoh.
Anda dapat mengunduh Templat Excel Beberapa Kriteria Kecocokan Indeks di sini - Templat Excel Kecocokan Indeks Beberapa KriteriaContoh # 1 - INDEX + Rumus MATCH
Bukan mayoritas fungsi pencarian pengguna excel di luar VLOOKUP, alasannya bisa sangat banyak. Bagaimanapun, mari kita memiliki pengantar sederhana untuk rumus ini sebelum kita pergi ke tingkat mahir.
Misalnya, lihat struktur data di bawah ini di excel.
Kami memiliki nama "Perwakilan Penjualan" dan nilai penjualannya masing-masing. Di sisi lain, kami memiliki daftar drop-down "Agen Penjualan" di sel D2.
Berdasarkan pilihan yang kami buat dari daftar drop-down jumlah penjualan harus muncul di sel E2.
Masalahnya adalah kita tidak dapat menerapkan rumus VLOOKUP karena nilai pencarian "Sales Rep" berada di sebelah kanan kolom hasil "Penjualan" sehingga dalam kasus ini kita dapat menggunakan rumus nilai pencarian kombinasi INDEX + MATCH.
INDEX mencari nilai nomor baris yang disebutkan di kisaran A2: A11 dan dalam kisaran ini, kita perlu menyediakan dari baris mana nilai penjualan tersebut berasal. Nilai baris ini didasarkan pada nama "Sales Rep" yang dipilih dalam daftar drop-down di excel, sehingga fungsi MATCH mencari nomor baris "Sales Rep" dalam rentang B2: B11 dan mengembalikan nomor baris dari nilai yang cocok .
Contoh # 2 - Beberapa Kriteria dalam Rumus INDEX + MATCH
Sekarang kami memiliki struktur data seperti di bawah ini.
Kami memiliki nilai penjualan bulanan "Sales Rep". Dari tabel ini, kita membutuhkan hasil yang dinamis seperti di sel A15 Saya telah membuat daftar drop-down "Sales Rep" dan di sel B14 saya telah membuat daftar drop-down "Bulan".
Berdasarkan pemilihan yang dilakukan dalam dua sel ini, rumus kami harus mengambil data dari tabel di atas.
Misalnya, jika saya memilih "Rep 8" dan "Apr" maka harus menunjukkan nilai penjualan "Rep 8" untuk bulan "Apr".
Jadi, dalam kasus ini, kita perlu mencocokkan baris dan kolom. Ikuti langkah-langkah di bawah ini untuk menerapkan rumus agar cocok dengan baris dan kolom.
Langkah 1: Buka fungsi INDEX di sel B15.
Langkah 2: Argumen pertama dari fungsi INDEX adalah "Array" yaitu dari rentang sel mana kita membutuhkan hasilnya. Jadi, dalam hal ini, kita membutuhkan nilai penjualan, jadi pilih rentang sel dari B2 hingga G11.
Langkah 3: Argumen berikutnya dari fungsi INDEX dari baris mana dari rentang yang dipilih kita membutuhkan hasilnya. Dalam hal ini, kita perlu sampai pada nomor baris "Sales Rep" berdasarkan pilihan yang dibuat di sel drop-down sel A15. Jadi untuk secara dinamis mengambil nomor baris berdasarkan fungsi MATCH pilihan terbuka.
Langkah 4: LOOKUP VALUE fungsi MATCH adalah “Sales Rep” jadi pilih sel A15 sebagai referensi.
Langkah 5: Array Pencarian akan menjadi rentang nama "Sales Rep" di tabel utama. Jadi pilih rentang sebagai A2 hingga A11.
Langkah 6: Jenis Kecocokan fungsi MATCH akan tepat jadi masukkan nol sebagai nilai argumen.
Langkah 7: Argumen selanjutnya dari fungsi INDEX adalah "Column Number" yaitu dari rentang sel yang dipilih dari kolom mana kita membutuhkan hasilnya. Ini tergantung pada bulan yang kita pilih dari daftar drop-down sel B14. Jadi untuk mendapatkan nomor kolom secara otomatis membuka fungsi MATCH lainnya.
Langkah 8: Kali ini nilai pencarian adalah nama bulan jadi pilih sel B14 sebagai referensi.
Langkah 9: Array pencarian akan menjadi rentang bulan sel di tabel utama yaitu dari B1 ke G1.
Langkah 10: Argumen terakhir adalah jenis pencocokan, pilih "Pencocokan Tepat" sebagai kriteria. Tutup dua tanda kurung dan tekan tombol enter untuk mendapatkan hasilnya.
Seperti yang dapat kita lihat di atas, kita telah memilih "Rep 6" dan "Apr" sebagai bulan dan formula kita telah mengembalikan nilai penjualan untuk bulan "Apr" untuk "Rep 6".
Catatan: Sel berwarna kuning adalah referensi untuk Anda.
Hal-hal untuk diingat
- Kombinasi INDEX + MATCH bisa lebih kuat daripada rumus VLOOKUP.
- INDEX & MATCH dapat mencocokkan judul baris dan kolom dan mengembalikan hasil dari tabel tengah.
- MATCH dapat mengembalikan nomor baris dan nomor kolom dari header tabel dari kedua baris & kolom.
