XLOOKUP adalah fungsi lookup generasi terbaru di Excel sebagai alternatif yang lebih baik dari VLOOKUP, HLOOKUP, dan bahkan kombinasi INDEX + MATCH. Dengan kemampuan mencari ke kanan, ke bawah, atau bahkan ke atas atau ke kiri (reverse search), XLOOKUP menjadi fungsi lookup paling fleksibel dan aman untuk digunakan dalam dataset modern.
Artikel ini membahas:
- konsep dasar XLOOKUP
- contoh dasar dan lanjutan
- penggunaan dalam kasus sebenarnya
- kesalahan umum dan cara menghindarinya
- perbandingan dengan fungsi lookup lain
- trik dan teknik yang jarang dibahas
File: fungsi-xlookup-excel.xlsx
Apa Itu Fungsi XLOOKUP di Excel?
XLOOKUP digunakan untuk mencari nilai dalam sebuah tabel dan mengembalikan hasil dari kolom atau baris lain yang sesuai. Fungsi ini dirancang untuk menggantikan VLOOKUP dan HLOOKUP dengan cara yang lebih fleksibel, lebih aman, dan lebih mudah dibaca.
Berbeda dengan VLOOKUP, XLOOKUP:
- dapat mencari ke kiri (VLOOKUP tidak bisa)
- tidak membutuhkan kolom indeks
- tidak rusak saat kolom ditambah atau dihapus
- dapat mencari dari bawah (reverse search)
- mendukung approximate match yang lebih aman
- dapat mengembalikan array beberapa kolom sekaligus
Sintak
XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
Penjelasan:
- lookup_value (wajib): Nilai yang dicari
- lookup_array (wajib): Kolom atau baris tempat pencarian dilakukan
- return_array (wajib): Kolom atau baris dari hasil yang ingin dikembalikan
- if_not_found (opsional): Pesan jika lookup_value tidak ditemukan (fallback system)
- match_mode (opsional): Jenis pencarian (exact, wildcard, approximate, regex)
- search_mode (opsional): Arah pencarian (atas ke bawah, bawah ke atas, kiri ke kanan, atau kanan ke kiri)
Kapan Menggunakan Fungsi XLOOKUP?
Berikut beberapa contoh kapan menggunakan XLOOKUP:
- Penjualan & Ritel: Menemukan harga atau nama produk berdasarkan kode barang
- Inventaris & Logistik: Memeriksa persediaan di gudang
- Keuangan & Akuntansi: Membandingkan angka anggaran dan aktual berdasarkan kode akun
- Pendidikan: Mengambil nilai tertinggi atau terendah berdasarkan nama atau kode siswa
- Manufaktur: Memetakan bahan baku ke pemasok dan waktu tunggu
- Pelaporan: Menggabungkan dua kelompok data berdasarkan nilai kunci yang sama
- Kasus lain: Pencarian dua arah, logika array, multi-kriteria, atau pencocokan berdasarkan perkiraan
Tips Menggunakan Rumus XLOOKUP
- Gunakan XLOOKUP untuk pencarian vertikal atau horizontal
- Default XLOOKUP adalah exact match, bukan approximate match (berdasarkan perkiraan)
- Gunakan argumen if_not_found untuk menghindari #N/A
- Gunakan match_mode = 2 untuk wildcard
- Gunakan search_mode = -1 untuk reverse search
- Gunakan logika array untuk multi‑kriteria
- Gunakan FILTER untuk mengembalikan banyak hasil
- Pastikan tipe data lookup_value dan lookup_array sama
- Jangan gunakan wildcard secara langsung di lookup_array
- XLOOKUP hanya mengembalikan kecocokan pertama
- Jangan gunakan XLOOKUP untuk fuzzy matching (tidak didukung)
- Pencarian bersifat case insensitive, mengabaikan huruf besar atau kecil.
Contoh Rumus XLOOKUP di Excel
Mencari Harga Barang
Rumus berikut melakukan lookup sederhana yang mencari harga barang berdasarkan kode.
=XLOOKUP(A9; A2:A6; B2:B6)Di mana:
- A9 adalah kode yang dicari.
- A2:A6 adalah rentang di mana kode dicari.
- B2:B6 adalah rentang dari mana nilai kembalian berasal.
Cocok untuk: Penjualan dan ritel.
Multi Kriteria: Data Karyawan Berdasarkan ID + Status
HR ingin mencari gaji karyawan tertentu berdasarkan kode karyawan dan status keaktifan.
=XLOOKUP(1; (A2:A6=A9)*(B2:B6=B9); C2:C6)Penjelasan:
- Rumus menggunakan perkalian untuk menampilkan kondisi logika And.
- Setiap logika—
(A2:A6=A9)atau(B2:B6=B9)—menghasilkan nilai TRUE atau FALSE - Perkalian mengubah TRUE atau FALSE menjadi 1 atau 0.
- Hasil akhir kriteria—1 atau 0—akan dibandingkan dengan lookup_value 1.
Cocok untuk: HR dan administrasi.
Mencari Harga Barang Termurah dari Beberapa Gudang
Sebuah perusahaan memiliki 3 gudang—Gudang A, B, C. Setiap gudang menyimpan produk yang sama dengan harga berbeda. Perusahaan ingin mengetahui gudang mana yang memiliki harga termurah untuk produk tertentu.
=XLOOKUP(MIN(B2:D2); B2:D2; B1:D1)Cocok untuk: Logistik dan inventaris.
Pencarian Terbalik: Mencari Transaksi Terbaru
Sebuah toko online ingin mencari transaksi terakhir pelanggan tertentu.
Toko tersebut menggunakan rumus XLOOKUP dengan reverse search atau search_mode = -1. Dengan pencarian ini, XLOOKUP akan mencari data dari bawah.
=XLOOKUP(A13; A2:A10; B2:B10; ; 0; -1)Cocok untuk: E-commerce, keamanan digital.
Lookup dengan Wildcard
Sekolah mencari murid-murid dengan nama mengandung andi dan mengembalikan nilainya.
XLOOKUP perlu menggunakan match_mode = 2, yaitu pencarian dengan wildcard.
=XLOOKUP("*"&D2&"*"; A2:A7; B2:B7; ; 2)Cocok untuk: Data cleaning.
Lookup Dua Arah (Baris + Kolom)
Sebuah perusahaan ingin mencari harga produk tertentu berdasarkan kode dan lokasi gudang.
Perusahaan menggunakan rumus XLOOKUP tersarang untuk melakukan pencarian dua arah, yaitu berdasarkan baris dan kolom.
=XLOOKUP(A7; A2:A4; XLOOKUP(B7; B1:D1; B2:D4))Cocok untuk: Data dengan tabel matriks.
XLOOKUP dengan Array Dinamis
Sebuah toko online ingin mencari tanggal dan nilai transaksi terakhir dari seorang pelanggan.
=XLOOKUP(A13; A2:A10; B2:C10; ; 0; -1)Rumus XLOOKUP menggunakan return_array yang terdiri dari 2 kolom.
Cocok untuk: Dashboard transaksi.
Mencari Supplier Tercepat
Sebuah toko ingin memasok ulang produk jaket. Toko ingin memastikan supplier mana yang mampu mengirimkan produk tercepat.
Rumus menggunakan FILTER untuk semua argumen wajib XLOOKUP, masing-masing untuk mendapatkan waktu pengiriman tercepat, array_lookup, dan array_return.
=XLOOKUP(MIN(FILTER(C2:C10; A2:A10=A13)); FILTER(C2:C10; A2:A10=A13); FILTER(B2:B10; A2:A10=A13))Cocok untuk: Manufaktur dan supply chain.
Mencari Produk dengan Rating Tertinggi
Seorang pelanggan ingin membeli tas sekolah dari toko online. Pelanggan ingin mengetahui supplier mana yang memiliki rating tertinggi untuk tas sekolah.
Rumus menggunakan MAX untuk mendapatkan rating serta FILTER agar hanya data tas sekolah yang diproses.
=XLOOKUP(MAX(FILTER(C2:C10; A2:A10=A13)); FILTER(C2:C10; A2:A10=A13); FILTER(B2:B10; A2:A10=A13))Cocok untuk: E-commerce.
Mencari Nilai Siswa Berdasarkan Nama + Kelas
Sekolah ingin mencari seorang siswa kelas 4 dengan nama yang memiliki unsur andi dan nilai tes di atas 90.
Rumus menggunakan kombinasi SEARCH dan wildcard, ISNUMBER, serta LEFT dalam multi-kriteria untuk mendapatkan siswa bersangkutan. ISNUMBER mengubah apa pun hasil SEARCH menjadi TRUE atau FALSE.
=XLOOKUP(1; (ISNUMBER(SEARCH("*andi*"; A2:A11)))*(LEFT(B2:B11; 1)="4")* (C2:C11>90); A2:C11)Cocok untuk: Pendidikan.
Mencari Kode Pelanggan dengan Pola Tertentu (Regex Lookup)
Sebuah pusat kebugaran ingin mencari pelanggan dengan kode yang memiliki 4 digit angka. Rumus menggunakan regex (regular expression) [0-9]{4} untuk mencari pelanggan tersebut. match_mode = 3 adalah pencarian regex.
=XLOOKUP(A12; A2:A9; B2:B9; "Tidak ada"; 3)Cocok untuk: Validasi kode pelanggan.
Kesalahan Umum Rumus XLOOKUP dan Solusinya
| Masalah | Penyebab | Solusi |
| Error #N/A | Tidak ada kecocokan | Gunakan argumen if_not_found |
| Hasil yang dikembalikan salah | Tertukar antara exact match dengan approximate match atau sebaliknya | Gunakan mode pencarian yang sesuai |
| Rumus multi-kriteria mengembalikan 0 | Penggunakan logika array tidak tepat | Gunakan XLOOKUP(1; (condition1)*(condition2); return_array) |
| Wildcard tidak berfungsi | match_mode tidak sama dengan 2 | Gunakan match_mode = 2 |
| Regex tidak berfungsi | match_mode tidak sama dengan 3 atau versi Excel tidak mendukung | Gunakan match_mode = 3 atau Excel 365 |
| Mengembalikan kolom yang salah | lookup_array dan return_array tidak sejajar atau sama ukuran | Kedua array harus sama ukuran atau urutan |
| Performa lambat | Array atau XLOOKUP bersarang terlalu besar atau banyak | Gunakan kolom pembantu atau kurangi ukuran array dengan FILTER |
| Hanya mengembalikan satu hasil yang cocok | XLOOKUP hanya mengembalikan kecocokan pertama | Gunakan FILTER |
| Mengembalikan hasil yang salah dalam pencarian 2 arah | Susunan XLOOKUP tidak tepat | Susun XLOOKUP induk dan tersarang dengan benar |
| Tidak berfungsi dengan tabel terstruktur | Referensi tabel tidak digunakan dengan benar | Gunakan referensi tabel data dengan benar |
| Gagal dengan tipe data campuran | Tipe data di lookup_value dan lookup_array tidak sama | Gunakan TEXT atau VALUE untuk menormalisasi tipe data atau samakan tipe data |
| Mengembalikan hasil yang cocok terakhir, bukan pertama | search_mode = -1 | Gunakan search_mode = 1 untuk pencarian maju |
| Rumus berubah saat disalin | Referensi relatif bergeser | Gunakan referensi absolut jika diperlukan |
XLOOKUP vs VLOOKUP vs INDEX MATCH vs FILTER
| Tugas | Fungsi Terbaik |
| Pencarian sederhana | XLOOKUP |
| Pencarian mundur | XLOOKUP |
| Pencarian dua arah | XLOOKUP / INDEX+MATCH |
| Multi‑kriteria | XLOOKUP / FILTER |
| Mengembalikan banyak baris | FILTER |
| Dashboard dinamis | FILTER + XLOOKUP |
| Kompatibilitas lama | VLOOKUP |
| Dataset besar | INDEX+MATCH (lebih cepat) |
Kesimpulan
XLOOKUP adalah fungsi lookup paling kuat dan fleksibel di Excel. Dengan kemampuan mencari ke segala arah, mendukung wildcard, reverse search, multi‑kriteria, dan array dinamis, XLOOKUP menggantikan hampir semua fungsi lookup lama.
Dengan memahami konsep, tips, dan contoh di artikel ini, kita dapat menggunakan XLOOKUP untuk berbagai kebutuhan — mulai dari UMKM, HR, logistik, hingga analisis data tingkat lanjut.