Rumus XLOOKUP dengan Multi Kriteria untuk Tabel Horizontal dan Vertikal

Pada era Excel modern seperti sekarang, kebutuhan untuk menampilkan data secara otomatis berdasarkan dua kriteria atau lebih menjadi semakin umum—terutama dalam dunia pendidikan, bisnis, dan manajemen data. Salah satu teknik terbaik untuk melakukan hal tersebut adalah menggunakan rumus XLOOKUP dengan multi kriteria. Fungsi ini memungkinkan kita mencari data berdasarkan dua kolom sekaligus, lalu mengisi tabel horizontal maupun vertikal secara otomatis. Dalam artikel ini, kita akan membahas secara lengkap bagaimana cara membuat rumus XLOOKUP dengan multi kriteria, bagaimana cara kerjanya, serta bagaimana menerapkannya pada tabel hasil.

Mengapa Memakai Rumus XLOOKUP dengan Multi Kriteria?

Sebelum ke teknis, kita fokus memperkenalkan konsep rumus XLOOKUP dengan multi kriteria, karena banyak pengguna Excel yang ingin melakukan pencarian data lebih fleksibel tanpa harus membuat kolom bantu. Ketika bekerja dengan data dua arah—misalnya baris berisi kelas dan kolom berisi mata pelajaran—kita membutuhkan rumus XLOOKUP dengan multi kriteria untuk memastikan hasil yang ditampilkan benar-benar sesuai dengan dua kondisi tersebut. Dengan cara ini, kita dapat mengisi tabel guru secara otomatis berdasarkan kombinasi Kelas dan Mapel tanpa memindahkan atau memanipulasi data secara manual. Kemampuan inilah yang membuat rumus XLOOKUP dengan multi kriteria menjadi solusi modern yang sangat efektif.

Memahami Struktur Tabel yang Digunakan

Sebelum membahas rumusnya, mari pahami dulu bentuk tabel sumber dan tabel hasilnya.

1. Tabel Sumber (Vertikal)

Berisi kolom:

• Kelas
• Mapel
• Guru

Contoh potongannya:

2. Tabel Hasil (Horizontal dan Vertikal)

Pada bagian kanan, tabel disusun dengan:

• Baris = Kelas
• Kolom = Mata Pelajaran
• Isi = Nama Guru

Struktur seperti ini tidak bisa ditangani oleh VLOOKUP atau HLOOKUP, sehingga kita membutuhkan rumus XLOOKUP dengan multi kriteria untuk mencari berdasarkan kelas sekaligus mapel. Berikut tabel utuhnya:

Memahami Rumus XLOOKUP dengan Multi Kriteria secara Bertahap

Dengan kondisi tabel di atas, berikut adalah contoh rumus yang digunakan:

=XLOOKUP(1; ($B$3:$B$14=$F3) * ($C$3:$C$14=G$2); $D$3:$D$14; "")

Mari kita uraikan bagian-bagian rumus ini agar pembaca mudah memahami bagaimana cara kerja rumus XLOOKUP dengan multi kriteria.

Bagian 1: Parameter Pencarian – “1”

XLOOKUP biasanya mencari teks atau angka tertentu. Namun untuk multi-kriteria, kita menggunakan teknik logical multiplication (perkalian logika).

Hasil dari:

($B$3:$B$14=$F3) * ($C$3:$C$14=G$2)

adalah array bernilai 1 dan 0, di mana:

• 1 berarti baris memenuhi kedua syarat
• 0 berarti tidak memenuhi

Karena itu, kita menginstruksikan XLOOKUP untuk mencari nilai 1.

Inilah inti dari rumus XLOOKUP dengan multi kriteria: menggabungkan dua kondisi menjadi satu array logika.

Bagian 2: Array Kondisi Pertama – Kolom Mapel

($B$3:$B$14=$F3)

Artinya:
“Cari baris di mana nilai Mapel pada tabel sumber sama dengan Mapel di kolom header tabel hasil.”

Jika benar → menghasilkan 1
Jika salah → menghasilkan 0

Bagian 3: Array Kondisi Kedua – Kolom Kelas

($C$3:$C$14=G$2)

Artinya:
“Cari baris di mana kelas sama dengan baris kelas tabel hasil.”

Bagian 4: Perkalian Dua Kondisi

Karena kondisi pertama dan kedua masing-masing menghasilkan array TRUE/FALSE (atau 1/0), maka:

(1 * 1 = 1) → baris cocok  
(1 * 0 = 0) → tidak cocok  
(0 * 1 = 0)  
(0 * 0 = 0)

Dengan cara ini, kombinasi dua syarat disatukan, sehingga XLOOKUP dapat menemukan baris yang tepat. Teknik ini adalah pondasi dari rumus XLOOKUP dengan multi kriteria pada Excel modern.

Bagian 5: Array Hasil – Kolom Guru

$D$3:$D$14

Ini adalah kolom yang berisi jawaban yang ingin ditampilkan, yaitu nama guru. Ketika XLOOKUP menemukan baris dengan hasil logika 1, nilai pada kolom inilah yang akan diambil.

Bagian 6: Nilai Jika Tidak Ditemukan

""

Kosongkan saja jika hasil tidak ditemukan.

Mengapa Rumus XLOOKUP Lebih Baik dari VLOOKUP?

Dalam banyak kasus, pengguna Excel lama menggunakan:

• VLOOKUP dengan kolom bantu
• INDEX + MATCH dua kolom
• Rumus panjang yang sulit dipahami pemula

Dengan rumus XLOOKUP dengan multi kriteria, semua ini bisa diganti dengan satu baris rumus yang:

• Lebih ringkas
• Lebih cepat dihitung
• Tidak butuh kolom bantu
• Lebih fleksibel untuk tabel horizontal dan vertikal

Inilah alasan mengapa XLOOKUP menjadi standar baru dalam manajemen data dua kriteria.

Cara Menerapkan Rumus XLOOKUP dengan Multi Kriteria pada Tabel

Pada tabel hasil seperti contoh:

• Kelas berada di kolom F
• Mapel berada di kolom G2, H2, I2, dst.

Rumus ditempatkan di setiap cell tabel hasil.

Contoh untuk sel G3:

=XLOOKUP(1; ($B$3:$B$14=$F3) * ($C$3:$C$14=G$2); $D$3:$D$14; "")

Setelah itu:

• Tarik ke samping → untuk mapel lainnya
• Tarik ke bawah → untuk kelas lainnya

Hasil akan tampil otomatis, seperti:

Anda bebas menambah kelas, memodifikasi mapel, atau mengembangkan tabel tanpa perlu mengubah struktur rumus.

Cara Menggunakan Rumus XLOOKUP untuk Tabel Vertikal

Jika tabel vertikal ingin kita buat juga berdasarkan dua kriteria—misalnya menampilkan nilai, wali kelas, ruang kelas, atau guru pengganti—rumusnya tetap sama:

=XLOOKUP(1; (kriteria1) * (kriteria2); hasil)

Contoh lain untuk tabel vertikal:

Menemukan guru berdasarkan:

• kelas = “VIII-A”
• mapel = “IPA”

Maka kita cukup menempelkan rumus:

=XLOOKUP(1; ($B$3:$B$14="IPA") * ($C$3:$C$14="VIII-A"); $D$3:$D$14; "")

Inilah fleksibilitas besar yang membuat rumus XLOOKUP dengan multi kriteria sangat berguna dalam berbagai struktur tabel.

Kesalahan Umum saat Menggunakan Rumus XLOOKUP

1. Tidak menaruh referensi absolut ($)
2. Lupa bahwa tabel hasil bersifat dua dimensi
3. Kriteria tidak cocok format (misal angka vs teks)
4. Menggunakan titik koma vs koma tidak sesuai regional Excel
5. Mengalikan kondisi tetapi tidak mencari angka 1

Pastikan semua langkah di atas diperhatikan.

Tips Mengoptimalkan Rumus XLOOKUP

• Gunakan fungsi LET() untuk merapikan rumus kompleks
• Pastikan range tabel terkunci
• Gunakan kolom yang konsisten untuk kriteria
• Tambahkan validasi data untuk mencegah salah input

Misalnya:

=LET(
  kelas; $B$3:$B$14;
  mapel; $C$3:$C$14;
  guru; $D$3:$D$14;
  XLOOKUP(1; (kelas=$F3) * (mapel=G$2); guru; "")
)

Kesimpulan

Dalam dunia Excel modern, penggunaan rumus XLOOKUP dengan multi kriteria adalah solusi terbaik ketika kita menghadapi tabel dua arah seperti contoh guru–kelas–mapel. Rumus ini memungkinkan kita mencari berdasarkan dua kriteria sekaligus tanpa membutuhkan kolom tambahan, dan bisa digunakan baik untuk tabel horizontal maupun vertikal.

Dengan memahami cara kerja bagian per bagian—mulai dari logika 1/0, penggabungan kriteria, hingga pencarian nilai—pengguna dapat dengan mudah mengembangkan tabel otomatis untuk sekolah, kantor, maupun bisnis lainnya. Jika dibandingkan metode lama seperti VLOOKUP atau INDEX-MATCH, jelas bahwa rumus XLOOKUP dengan multi kriteria jauh lebih efisien, lebih sederhana, dan lebih mudah dipahami.

Jika Anda sedang membangun sistem jadwal, rekapan guru, laporan kelas, atau dashboard Excel lainnya, maka rumus XLOOKUP dengan multi kriteria adalah fondasi penting yang akan sangat membantu pekerjaan Anda menjadi lebih cepat dan lebih akurat.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Rumus XLOOKUP dengan Multi Kriteria untuk Tabel Horizontal dan Vertikal 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!

—