3 Alternatif Rumus Cari Data Banyak Kriteria

Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang kemudian mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diharapkan kolom bantu yang berisikan data campuran (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan memakai rumus pencari data banyak kriteria tanpa harus memakai kolom bantu.

Ada beberapa alternative formula yang sanggup digunakan. Berikut 3 diantaranya:

INDEX MATCH
OFFSET MATCH
INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibentuk dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel

Anggaplah kita mempunyai tabel data harga buah-buahan dari aneka macam supplier. Label kolom tabel dari kiri ke kanan adalah:

Kolom A = No
Kolom B = Buah
Kolom C = Supplier
Kolom D = Harga

Selanjutnya contohnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam kiprah pencarian data ini, yaitu:

Kriteria 1 = nama buah
Kriteria 2 = nama suplier

Pertanyaannya: Bagaimana rumusnya untuk mendapat data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut sanggup digambarkan sebagai berikut:

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat bahwasanya akan terasa kalau bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya memakai 2 kriteria, namun intinya prinsip kerjanya yakni sama, baik memakai 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini memakai rumus array, sehingga dikala selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.

Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH

Salah satu rumus terpopuler yang biasa dipakai untuk pencarian data yakni kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita sanggup memakai rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Cara Kerja Rumus INDEX MATCH

Operator ampersand (&) dipakai untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom kolom buah (B2:B8) dan supplier (C 2:C8)
Fungsi MATCH berkhasiat untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
Angka yang diperoleh dari fungsi MATCH kemudian dipakai oleh fungsi INDEX sebagai row_index dalam range D2:D8.
Dan sel baris ke-3 dalam range D2:D8 yakni sel D4, sementara itu sel D4 berisi nilai 8000.
Sehingga hasil final yakni 8000.

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berkhasiat untuk mendapat referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.

Dengan menggabungkannya dengan fungsi MATCH, maka kita akan memilih berapa baris jarak sel yang dicari dari sel acuan, kemudian mendapat nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}

Cara Kerja Rumus OFFSET-MATCH

Fungsi MATCH berperan sama ibarat halnya dalam rumus INDEX MATCH, dimana fungsi MATCH dalam contoh tersebut menghasilkan angka 3.
Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana sanggup dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapat nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
Dan sel dimaksud dengan kriteria tersebut yakni sel D4
Sementara itu sel D4 berisi nilai 8.000. Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi INDIRECT berkhasiat untuk mendapat nilai secara tidak eksklusif dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS dipakai untuk menciptakan string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi MATCH berkhasiat untuk mendapat nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapat index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka 3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapat angka 4.
Index Kolom dalam spreadsheet didapatkan memakai sumbangan fungsi COLUMN, dan kolom D2:D8 yakni 4 (kolom D)
Ringkasnya rumus tersebut sanggup dikonvert menjadi =INDIRECT(ADDRESS(4,4))
ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapat nilai dalam sel $D$4
Dan hasilnya yakni 8.000

Ringkasan.

Cari data dengan 2 kriteria atau lebih sanggup dilakukan tanpa memakai kolom bantu, yaitu memakai rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya sanggup dipakai sesuai kebutuhan fungsi lainnya.

Demikian agar bermanfaat.

Salam

Silahkan dibaca juga artikel Belajar Excel Lainnya: