2 Cara Mencari Data dengan Banyak Kolom di Excel dengan Rumus

Dalam pengolahan data di Microsoft Excel, sering kali kita dihadapkan pada situasi di mana sebuah nilai dapat muncul di banyak kolom yang berbeda. Misalnya, dalam sebuah tabel daftar peserta lomba dengan berbagai cabang olahraga, kita ingin mengetahui peserta tertentu berasal dari kelas mana. Tantangannya adalah data nama peserta tidak disusun hanya dalam satu kolom, melainkan tersebar di banyak kolom sesuai cabang lomba.

Pada kondisi seperti ini, kita membutuhkan cara mencari data dengan banyak kolom agar bisa menemukan informasi dengan cepat dan akurat. Untungnya, Excel menyediakan beberapa rumus yang cukup powerful untuk menyelesaikan masalah ini. Dalam artikel ini, kita akan membahas dua metode utama, yaitu menggunakan kombinasi INDEX + MATCH + MMULT serta menggunakan XLOOKUP + BYROW + LAMBDA.

Keduanya mampu menjawab kebutuhan cara mencari data dengan banyak kolom dengan hasil yang sama, namun mekanisme dan keunggulannya sedikit berbeda. Mari kita bahas lebih detail.

Contoh Kasus Data

Perhatikan tabel berikut:

Di tabel ini, kolom C sampai E berisi nama peserta lomba berdasarkan cabang olahraga, sementara kolom B berisi kelas masing-masing peserta. Tugas kita adalah menemukan kelas dari peserta tertentu yang namanya dicari pada kolom lain.

Misalnya, jika kita mencari nama Andi, maka rumus harus menghasilkan output VIII, karena Andi berasal dari kelas VIII.

1. Cara Mencari Data dengan Banyak Kolom Menggunakan INDEX + MATCH + MMULT

Rumus yang digunakan:

=INDEX($B$3:$B$5;MATCH(1;MMULT(--($C$3:$E$5=G3);TRANSPOSE(COLUMN($C$3:$E$5)^0));0))

Mari kita pecah langkah-langkahnya agar lebih mudah dipahami:

1. $C$3:$E$5=G3
   Bagian ini akan membandingkan seluruh isi range C3:E5 dengan nilai yang kita cari di sel G3 (misalnya “Andi”). Hasilnya berupa array TRUE/FALSE. Contoh hasil:

   {FALSE, FALSE, FALSE;
    FALSE, TRUE, FALSE;
    FALSE, FALSE, FALSE}

2. –(…)
   Tanda minus ganda mengubah nilai TRUE menjadi 1 dan FALSE menjadi 0. Hasil:

   {0,0,0;
    0,1,0;
    0,0,0}

3. MMULT(…;TRANSPOSE(COLUMN($C$3:$E$5)^0))
   Fungsi MMULT menghitung perkalian matriks. Di sini, COLUMN($C$3:$E$5)^0 menghasilkan array vektor {1;1;1} yang ditransposisi. Dengan MMULT, kita menjumlahkan baris untuk menemukan posisi peserta yang cocok. Hasilnya:

   {0;
    1;
    0}

4. MATCH(1;…;0)
   MATCH mencari angka 1 pada hasil array di atas. Dalam contoh ini, angka 1 berada di baris ke-2 (kelas VIII).
5. INDEX($B$3:$B$5;…)
   INDEX mengambil data kelas sesuai baris yang ditemukan. Jadi hasil akhirnya adalah VIII.

Inilah kelebihan metode pertama: fleksibel dan bisa digunakan di Excel versi lama (Excel 2016 ke bawah) yang belum mendukung fungsi dinamis.

Dengan teknik ini, kita sudah menemukan cara mencari data dengan banyak kolom tanpa perlu menggabungkan tabel secara manual.

2. Cara Mencari Data dengan Banyak Kolom Menggunakan XLOOKUP + BYROW + LAMBDA

Rumus yang digunakan:

=XLOOKUP(TRUE;BYROW($C$3:$E$5;LAMBDA(r;ISNUMBER(MATCH(G3;r;0))));$B$3:$B$5)

Langkah penjelasan:

1. MATCH(G3;r;0)
   MATCH akan mencari nilai di G3 (misalnya “Cecep”) dalam setiap baris r (range yang sedang diperiksa). Jika ketemu, hasilnya angka posisi; jika tidak, error.
2. ISNUMBER(…)
   Mengubah hasil MATCH menjadi TRUE jika ketemu (karena hasil MATCH berupa angka), atau FALSE jika tidak.
3. LAMBDA(r; …)
   LAMBDA dipakai di sini untuk membuat fungsi kecil yang diterapkan ke setiap baris range C3:E5.
4. BYROW($C$3:$E$5;LAMBDA(…))
   Fungsi ini mengevaluasi setiap baris dan menghasilkan array logika. Misalnya, jika Cecep ada di baris 2, hasil array akan seperti:

   {FALSE;
    TRUE;
    FALSE}

5. XLOOKUP(TRUE; … ; $B$3:$B$5)
   XLOOKUP mencari nilai TRUE dari hasil BYROW dan mengembalikan kelas pada kolom B yang sesuai. Hasilnya: VIII.

Metode ini lebih modern dan lebih ringkas dibanding cara pertama. Namun, perlu dicatat bahwa rumus ini hanya bisa digunakan di Excel 365 atau Excel 2021 yang sudah mendukung fungsi BYROW dan LAMBDA. Berikut hasilnya:

Perbandingan Kedua Metode

• INDEX + MATCH + MMULT
• Kelebihan: Bisa digunakan di Excel versi lama.
• Kekurangan: Rumus terlihat rumit bagi pemula.
• XLOOKUP + BYROW + LAMBDA
• Kelebihan: Rumus lebih jelas, mudah dibaca, memanfaatkan fungsi dinamis terbaru.
• Kekurangan: Hanya tersedia di Excel 365/2024.

Keduanya merupakan solusi jitu untuk cara mencari data dengan banyak kolom, sehingga Anda bisa memilih sesuai dengan versi Excel yang digunakan.

Kesimpulan

Mengelola data yang tersebar di beberapa kolom bisa menjadi tantangan besar jika hanya mengandalkan pencarian manual. Namun, dengan memahami rumus INDEX + MATCH + MMULT maupun XLOOKUP + BYROW + LAMBDA, kita dapat menyelesaikan masalah ini dengan cepat dan efisien.

Baik metode lama maupun metode modern sama-sama menjawab kebutuhan kita dalam menemukan cara mencari data dengan banyak kolom di Excel. Dengan teknik ini, Anda bisa mempercepat pekerjaan, mengurangi kesalahan manual, dan membuat pengolahan data lebih profesional.

Jadi, jika Anda sering berurusan dengan data yang kompleks, pastikan untuk mencoba kedua cara ini. Dijamin, setelah memahami logikanya, Anda akan semakin mudah menemukan cara mencari data dengan banyak kolom untuk berbagai kasus nyata di tempat kerja Anda.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Mencari Data dengan Banyak Kolom di Excel dengan Rumus INDEX dan XLOOKUP ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!