Rumus excel dengan banyak kriteria

Rumus excel untuk Pencarian /Lookup banyak kriteria pada microsft excel menggunakan fungsi INDEX-MATCH alternatif Fungsi Vlookup, Hlookup dan Lookup

Mencari data dengan satu kriteria sudah biasa. hal tersebut bisa kita atasi dengan menggunakan fungsi Lookup, HLookup maupun VLookup.

Lalu bagaimana jika kita perlu melakukan pencarian data dengan dua atau lebih kriteria? Adakah rumus excel yang dapat melakukan hal tersebut?

• Mencari Data Dengan Banyak Kriteria Pada Excel
• Lookup Banyak Kriteria Dengan Rumus INDEX dan MATCH
• Penjelasan Rumus Lookup banyak Kriteria

Mencari Data Dengan Banyak Kriteria Pada Excel

Melakukan Lookup Banyak kriteria bukanlah hal mustahil. Ada banyak cara bisa kita lakukan dengan excel untuk mengtasi problem pencarian data dengan banyak kriteria tersebut. Salah satunya adalah dengan menggunakan rumus excel gabungan antara fungsi INDEX dan MATCH.

Seperti yang sudah saya tulis sebelumnya bahwa fungsi INDEX berfungsi untuk memberikan nilai pada baris yang ditunjuk. sedangkan fungsi MATCH berfungsi untuk mencari pada baris berapa data yang sesuai. Jika masih belum faham silahkan baca artikel tentang fungsi Excel tersebut sebelumnya.

Sebagai contoh kali ini kita akan membuat sebuah Tabel data penghuni gedung pada microsoft excel yang berisi dengan empat (4) kolom. Yakni kolom gedung, lantai, nomor kamar dan nama penghuni. kemudian lengkapi tabel tersebut seperti gambar berikut:

Sebelum membaca tutorial excel ini lebih jauh, kami sarankan anda membaca tutorial Cara Menggunakan rumus Index-Match terlebih dahulu.

Dalam kasus Lookup banyak kriteria ini kita ingin mencari tahu siapa saja penghuni kamar dengan kriteria gedung, lantai dan nomor kamarnya.

Pada tabel tersebut hasil lookup kita letakkan pada sel D12 yang berwarna hijau, sedangkan kriteria atau syarat pencariannnya kita letakkan masing-masing pada sel A12, B12 dan C12 yang berwarna merah.

Pada contoh tersebut Nilai yang akan kita cari adalah penghuni gedung B pada lantai 2 dan nomor kamar 1.

Tuliskan rumus berikut pada sel D12:

=INDEX(D2:D9;MATCH(A12&B12&C12;A2:A9&B2:B9&C2:C9;0))

Kemudian akhiri dengan menekan tombol Ctrl + Shift + Enter secara bersamaan setelah menuliskan rumus diatas. Sehingga rumus akan nampak diapit {...}

{=INDEX(D2:D9;MATCH(A12&B12&C12;A2:A9&B2:B9&C2:C9;0))}

Tanda {...} tidak ditulis secara manual. Tanda {...} tersebut menunjukkan bahwa rumus excel tersebut merupakan rumus array atau sering juga disebut rumus CSE.

Cara eksekusinya adalah dengan menuliskan rumus pada cell, misal:

=INDEX(D2:D9;MATCH(A12&B12&C12;A2:A9&B2:B9&C2:C9;0))

kemudian akhiri dengan menekan tombol Ctrl + Shift + Enter untuk memunculkan tanda {...}.

Penjelasan Rumus Lookup banyak Kriteria

Pada rumus excel diatas kita memakai dua fungsi yakni fungsi INDEX dan Fungsi MATCH.

Sintaks fungsi INDEX adalah:

INDEX(array; row_num; [column_num])

Dalam rumus tersebut array dari fungsi INDEKS adalah D2:D9 dimana pada kolom ini nilai yang kita cari berada. Sedangkan row_number atau nomor barisnya adalah hasil dari fungsi MATCH. dan [column_num] nya kita abaikan karena argument ini bersifat opsional dan pada kasus ini tidak perlu kita tuliskan.

Sedangkan sintaks fungsi MATCH adalah:

MATCH(lookup_value; lookup_array; [match_type])

Dalam rumus LookUp banyak kriteria diatas nilai yang kita cari dari fungsi MACTH atau lookup_value nya adalah gabungan dari kriteria pencarian yakni: A12&B12&C12. Jika anda masih bertanya tentang apa maksud dari tanda & pada rumus tersebut silahkan pelajari tentang Operator Teks Excel.

Argument lookup_array nya adalah A2:A9&B2:B9&C2:C9. Array inilah yang akan digunakan fungsi MATCH untuk mendapat nomor baris atau row yang sesuai. Sedangkan argument [match_type]bernilai 0 (nol) dengan maksud bahwa pencarian bersifat exact atau sama persis.

Menggunakan Rumus Excel Array seperti ini sebenarnya kurang bagus apabila data yang kita olah cukup besar. Sebab rumus array biasanya memberatkan kinerja komputer kita. Jadi jika data yang kita olah ribuan misalnya. sedikit bersabarlah. ehehehehehee.... atau silahkan coba-coba dengan lookup banyak kriteria menggunakan macro. InsyaAllah akan saya tulis lain waktu jika pembahasan sudah sampai tentang VBA. Untuk sementara silahkan googling dulu jika memang memebutuhkan.

File contoh artikel ini bisa didownload pada tombol dibawah ini:

Dropbox
GDrive

*Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia

Masih ada pertanyaan? silahkan sampaikan di kolom komentar.

OUR LATEST VIDEOS