Cara Mencari Data di Banyak Kolom Berdasarkan Kriteria Tertentu dengan Rumus Excel

Mengelola dan menganalisis data dalam lembar kerja Excel dapat menjadi tugas yang rumit, terutama ketika kita perlu mencari data di banyak kolom berdasarkan kriteria tertentu. Salah satu cara efektif untuk mencari data di banyak kolom ini adalah dengan menggunakan formula array. Dalam artikel ini, kita akan menjelaskan langkah-langkah untuk mencari data dengan tepat menggunakan formula array di Excel.

Cara Mencari Data di Banyak Kolom Berdasarkan Kriteria Tertentu

Langkah 1: Memahami Fungsi INDEX dan ROW

Formula array yang umum digunakan untuk mencari data di banyak kolom ini adalah menggunakan fungsi INDEX dan ROW. Fungsi INDEX digunakan untuk mengambil nilai dari suatu rentang sel, sementara fungsi ROW digunakan untuk mendapatkan nomor baris.

Sebagai contoh, kita akan mencari klasifikasi arsip berdasarkan kodenya. Kenapa ini menjadi tidak cukup menggunakan VLOOKUP atau INDEX MATCH adalah karena kode arsip yang ada untuk satu klasifikasi arsip masing-masing memiliki 5 kode. Untuk lebih jelasnya berikut adalah contoh tabelnya:

Dan tugas kita adalah mencari klasifikasinya berdasarkan kategori kode arsip yang ada di sel B12 sampai dengan B15.

Langkah 2: Menyusun Kriteria Pencarian

Dalam contoh di atas, kita memiliki kriteria pencarian yaitu nilai di sel B12 harus sama dengan nilai dalam rentang sel C3:G9. Anda dapat mengganti kriteria ini sesuai dengan kebutuhan Anda. Pastikan untuk menggantinya dengan kriteria yang sesuai dengan data Anda.

Untuk tabel contoh di atas saya sudah menggunakan dropdown list yang dihasilkan dengan Data Validation. Untuk cara membuat dropdown list silahkan Anda cari di blog ini. Saya sering sekali membahas soal pembuatan dropdown list ini. Atau jika ingin praktis, silahkan download saja file contoh ini yang link downloadnya ada di akhir artikel.

Langkah 3: Mengetikkan Formula Array

Setelah menentukan kriteria pencarian, masukkan rumus array di sel tempat Anda ingin hasilnya muncul. Sebagai contoh, kita akan memunculkan hasil pencariannya di sel C12 sampai C15. Ingat untuk menyelesaikan rumus harus dengan menekan Ctrl + Shift + Enter agar dianggap sebagai formula array oleh Excel. Jika benar, rumus yang dihasilkan akan secara otomatis diapit oleh tanda kurung kurawal.

Dan berikut adalah formula array yang bisa kita gunakan untuk sel C12:

=INDEX($B$3:$B$9;MAX((B12=($C$3:$G$9))*(ROW($1:$7))))

Jika sudah, untuk mengisi sel di bawahnya, copy dan paste formula array tersebut atau arahkan kursor ke pojok sel C12 kemudian setelah muncul tanda “+” klick dan drag rumus ke sel di bawahnya. Dan berikut adalah hasilnya:

Langkah 4: Memahami Hasil

Hasil dari formula array ini adalah nilai yang sesuai dengan kriteria pencarian dari kolom yang ditentukan. Formula akan mencari nilai terakhir yang memenuhi kriteria dalam rentang yang diberikan. Untuk lebih mudahnya berikut adalah penjelasan lengkapnya:

Formula array Excel yang kita buat ini menggunakan fungsi INDEX, MAX, dan ROW untuk mencari data dalam banyak kolom berdasarkan kriteria tertentu. Mari kita jelaskan bagian per bagian:

1. INDEX($B$3:$B$9;…): Ini adalah fungsi INDEX di mana kita mencari nilai di dalam rentang sel B3:B9.
2. MAX((B12=($C$3:$G$9))*(ROW($1:$7))): Ini adalah bagian yang lebih kompleks. Mari kita pecah menjadi dua bagian: a. (B12=($C$3:$G$9)): Ini adalah bagian pertama dari ekspresi yang menghasilkan array boolean. Ini memeriksa apakah nilai di sel B12 sama dengan nilai di rentang sel C3:G9. Hasilnya adalah array boolean yang menunjukkan di mana kondisi tersebut terpenuhi. b. ROW($1:$7): Ini adalah bagian kedua yang menghasilkan array dengan nomor baris dari 1 hingga 7. c. (B12=($C$3:$G$9))*(ROW($1:$7)): Kedua array di atas dikalikan, menghasilkan array yang berisi nomor baris di mana kondisi (nilai di B12 sama dengan nilai di C3:G9) terpenuhi, dan di mana baris-baris tersebut ada dalam rentang 1-7. d. MAX(…): Fungsi MAX digunakan untuk menemukan nilai maksimum dari array yang dihasilkan sebelumnya, sehingga kita mendapatkan nomor baris terbesar di mana kondisi terpenuhi.
3. INDEX($B$3:$B$9;MAX(…)): Fungsi INDEX kemudian digunakan untuk mengambil nilai dari sel B3:B9 pada baris yang sesuai dengan nomor baris maksimum yang ditemukan sebelumnya.

Jadi, keseluruhan formula ini mencari nilai dalam kolom B (dari B3 hingga B9) berdasarkan pada kriteria bahwa nilai di B12 sama dengan nilai di antara C3 dan G9, dan mengambil nilai dari baris terakhir di mana kondisi tersebut terpenuhi. Formula ini adalah formula array karena melibatkan operasi pada seluruh rentang sel sekaligus. Jika Anda mengetikkan formula ini, pastikan untuk menyelesaikannya dengan menekan tombol Ctrl + Shift + Enter agar dianggap sebagai formula array oleh Excel.

Kesimpulan

Dengan menggunakan formula array di Excel, mencari data di banyak kolom berdasarkan kriteria tertentu dapat dilakukan dengan lebih efisien. Memahami fungsi INDEX, ROW, dan cara menyusun kriteria pencarian akan membantu Anda mengoptimalkan analisis data Anda. Dengan mengikuti langkah-langkah dalam artikel ini, Anda dapat meningkatkan kemampuan Anda dalam mengeksplorasi dan mengekstrak informasi yang dibutuhkan dari lembar kerja Excel Anda.

Dan sebagai penutup, bagi yang membutuhkan file Excel yang digunakan sebagai contoh Cara Mencari Data di Banyak Kolom Berdasarkan Kriteria Tertentu dengan Rumus Excel di atas bisa diunduh melalui tautan di bawah ini:

Terakhir, jika ingin mempelajari langkah demi langkah tentang Microsoft Excel secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana Anda akan melihat bagaimana rumus-rumus Excel dan juga tips Excel lainnya bekerja. Semoga bermanfaat!