Fungsi seperti VLOOKUP dan HLOOKUP memiliki keterbatasan: tidak bisa mencari ke kiri, tidak fleksibel terhadap kesalahan, dan hasil bisa tidak sesuai saat data berubah.
Fungsi XLOOKUP adalah solusinya. Fungsi ini mampu menggantikan VLOOKUP, HLOOKUP, dan LOOKUP dengan sintaks lebih intuitif, pencarian dua arah, penanganan kesalahan bawaan, dan dukungan wildcard.
Artikel ini membahas sintaks, contoh penggunaan, dan tips penting penggunaan XLOOKUP.
Unduh file berikut untuk memahami screenshot di artikel ini lebih baik.
File: fungsi-xlookup-excel.xlsx.
Pengertian dan Fungsi XLOOKUP di Excel
XLOOKUP adalah fungsi pencarian yang mengembalikan nilai dari array atau rentang berdasarkan nilai kunci.
XLOOKUP mendukung pencarian vertikal dan horizontal, serta bisa mencari ke kiri, kanan, atas, atau bawah.
Fungsi ini dapat digunakan di Excel 365, Excel Web, dan Excel 2019 ke atas.
Sintaks XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
| Parameter | Keterangan | Wajib | Opsi |
|---|---|---|---|
| lookup_value | Nilai yang dicari | Ya | |
| lookup_array | Array atau range tempat pencarian | Ya | |
| return_array | Array atau range tempat hasil kembalian | Ya | |
| if_not_found | Nilai alternatif jika hasil tidak ditemukan | Tidak | |
| match_mode | Mode pencocokan | Tidak |
|
| search_mode | Arah pencarian | Tidak |
|
Kapan dan Mengapa Menggunakan XLOOKUP di Excel
Gunakan XLOOKUP saat:
- Membutuhkan pencarian 2 arah (ke kiri, kanan, bawah, atau atas).
- Mengembalikan data yang berada di kiri atau atas kolom atau baris pencarian.
- Membutuhkan rumus dengan penanganan kesalahan yang baik.
- Data bersifat dinamis di mana kolom atau baris mungkin berubah.
- Menghindari keterbatasan VLOOKUP, HLOOKUP, and LOOKUP.
- Mengurangi kompleksitas fungsi lookup tersarang.
Tips Penting agar Rumus XLOOKUP Berfungsi Baik
- Gunakan referensi absolut (contoh: $A$2:$A$6) agar referensi tidak berubah saat disalin
- Pastikan lookup_array dan return_array memiliki dimensi yang sama
- Gunakan if_not_found untuk menangani error dengan lebih baik
- Gunakan match_mode = 2 untuk pencarian parsial (wildcard)
- Gunakan search_mode = -1 untuk mencari dari bawah ke atas
- XLOOKUP mengembalikan nilai pertama yang ditemui
- Hindari rumus terlalu kompleks
- Hindari rentang terlalu besar untuk menjaga performa
- Gunakan pencarian binari jika rentang pencarian dan kembalian besar (ratusan baris bahkan lebih)
- Tambahkan dokumentasi bila perlu
Contoh Penggunaan Rumus XLOOKUP di Excel
Mencari Nama Karyawan Berdasarkan Nomor Identitas
Rumus berikut mencari nama karyawan berdasarkan nomor identitas.
=XLOOKUP(D3; $A$2:$A$6; $B$2:$B$6; "Tidak ada")Berikut keterangan rumus:
D3(lookup_value): Referensi terhadap nilai yang dicari.A2:A6(lookup_array): Range di mana nilai D3 dicari.B2:B6(return_array): Range dari mana nilai kembalian berasal.Tidak ada(if_not_found): Nilai yang dikembalikan jika pencarian tidak menemukan hasil.
lookup_array dan return_array menggunakan referensi absolut ($A$2:$A$6 dan $B$2:$B$6) agar referensi tetap sama saat rumus disalinkan ke cell lain.
Mengambil Data dari Kanan di Excel
XLOOKUP mampu mengambil data di atas atau kiri dari range pencarian (lookup_array). Kondisi ini dimungkinkan karena XLOOKUP menggunakan referensi sebagai penunjuk lokasi kembalian, alih-alih angka positif.
Rumus berikut mencari nama karyawan berdasarkan nomor identitas. Kolom nama berada di sebelah kiri kolom identitas.
=XLOOKUP(D2; B2:B6; A2:A6; "Tidak ada")
Mencari Data dari Bawah: Menemukan Login Terakhir
XLOOKUP mampu mencari dari bawah ke atas untuk tabel vertikal atau kanan ke kiri untuk tabel horizontal dengan search_mode = -1.
Rumus berikut mencari kapan terakhir kali Bethari login.
=XLOOKUP(D2; B2:B8; A2:A8; "Tidak pernah login"; ; -1)
XLOOKUP dengan Wildcard untuk Pencarian Parsial
Rumus berikut mencari email dengan nama domain @excelandor.com dan mengembalikan penggunanya. Wildcard asterisk (*) mewakili sebagian dari keseluruhan badan teks dan dapat berupa nilai apa pun.
Rumus menggunakan match_mode = 2 yang berarti rumus melakukan pencarian wildcard.
=XLOOKUP("*@excelandor.com";B2:B6; A2:A6; "Tidak ada"; 2)
Baca: Wildcard Excel.
Rumus XLOOKUP Bersarang untuk Pencarian Bertingkat
Rumus berikut menggunakan rumus XLOOKUP bertingkat untuk mencari penjualan Q3 (A8) bagi Pisang (B8).
=XLOOKUP(B8; A2:A3; XLOOKUP(A8; B1:D1; B2:D3); "Tidak ada")
XLOOKUP dengan Kriteria: Menampilkan Beberapa Kolom
Rumus berikut mengembalikan kuantitas penjualan Pepaya oleh Sudianto. Rumus menggunakan boolean conditional sebagai kriteria dan mengembalikan array sebagai hasil.
=XLOOKUP(1; (A2:A9="Sudianto")*(B2:B9="Pepaya"); A2:C9; "Tidak ada"; ;1)
XLOOKUP dengan MATCH, dan INDEX Untuk Kolom Dinamis
Rumus di bawah menggunakan konsep yang sama dengan XLOOKUP bertingkat. Perbedaannya, rumus menggantikan XLOOKUP tersarang dengan kombinasi MATCH dan INDEX.
=XLOOKUP("Mangga"; A2:A4; INDEX(A2:E4; ; MATCH("Mar"; A1:E1; 0)))
XLOOKUP dan SORTBY Mencari Berdasarkan Peringkat Tertinggi
Rumus berikut mengasumsikan bahwa Agung adalah salah satu peserta dengan nilai tertinggi. Dengan menggunakan SORTBY untuk mengurutkan data secara descending, rumus dapat menghemat waktu pencarian. Ingat bahwa XLOOKUP mengembalikan nilai terawal yang ditemui.
=XLOOKUP(D2; SORTBY(A2:A6; B2:B6; -1); B2:B6)
Mengapa Rumus XLOOKUP Tidak Berfungsi?
Ada beberapa penyebab umum dan solusi kesalahan atau error rumus XLOOKUP.
| Kesalahan | Penyebab | Solusi |
|---|---|---|
| #N/A | Nilai tidak ditemukan atau tipe data antara lookup_value dan lookup_array tidak sama. |
|
| #VALUE! | lookup_array dan return_array berbeda ukuran | Pastikan kedua array memiliki jumlah kolom dan baris yang sama |
| #REF! | Rentang terlalu besar, rumus lambat | Batasi ukuran rentang |
| Rumus lambat | lookup_array dan return_array terlalu besar | Batasi ukuran lookup_array dan return_array sesuai kebutuhan |
| Hasil tidak sesuai |
|
|
| XLOOKUP tidak ada | Hanya tersedia di Excel 2019 ke atas, Excel Web, dan Excel 365 |
|
Perbandingan XLOOKUP vs VLOOKUP, HLOOKUP
Dengan melihat beberapa perbandingan XLOOKUP dengan VLOOKUP dan HLOOKUP berikut, kita dapat melihat kelebihan XLOOKUP dari fungsi-fungsi lainnya.
| XLOOKUP | VLOOKUP | HLOOKUP | |
|---|---|---|---|
| Arah pencarian | Dapat melakukan pencarian secara vertikal atau horizontal | Hanya vertikal | Hanya horizontal |
| Posisi hasil | Fleksibel. Bisa dari kiri, kanan, atas, atau bawah kolom pencarian | Hanya kanan | Hanya bawah |
| Pengambilan nilai | Range | Angka indeks | Angka indeks |
| Pencocokan default | Exact match | Approximate match | Approximate match |
| Penanganan error | Tersedia dengan if_not_found | Perlu IFNA atau IFERROR | Perlu IFNA atau IFERROR |
| Default nilai tumpahan (Spill) | Didukung langsung | Terbatas | Terbatas |
| Wildcard | Tersedia khusus dengan match_mode = 2 | Terbatas | Terbatas |
Kesimpulan
Fungsi XLOOKUP di Excel adalah evolusi pencarian data yang selama ini terbatas oleh VLOOKUP dan HLOOKUP. Dengan sintaks yang lebih intuitif, pencarian dua arah, penanganan error bawaan, dan dukungan wildcard, XLOOKUP adalah alat fleksibel untuk laporan, dashboard, dan analisis data.
Dengan memahami parameter seperti match_mode dan search_mode, serta menggabungkannya dengan fungsi lain seperti SORTBY, INDEX, dan MATCH, kita bisa membangun rumus pencarian yang dinamis, efisien, dan tahan terhadap perubahan struktur data.