XLOOKUP
Diperbarui
XLOOKUP mencari sebuah nilai di satu rentang, lalu mengembalikan nilai pada posisi yang sama dari rentang lain. Arahnya bebas, ke kiri maupun kanan, dan Anda bisa menentukan pesan bila tidak ketemu. Pakai sebagai pengganti VLOOKUP di file baru.
Formula dasar
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argumen | Arti | Wajib? |
|---|---|---|
lookup_value | Nilai yang dicari | Ya |
lookup_array | Rentang (satu kolom atau satu baris) tempat mencari | Ya |
return_array | Rentang yang hasilnya diambil, tinggi atau lebarnya sama dengan lookup_array | Ya |
if_not_found | Hasil bila tidak ada yang cocok, menggantikan #N/A | Tidak |
match_mode | 0 persis (default), -1 persis atau terdekat yang lebih kecil, 1 persis atau terdekat yang lebih besar, 2 wildcard | Tidak |
search_mode | 1 dari atas (default), -1 dari bawah, 2 / -2 pencarian biner | Tidak |
Contoh: harga, stok, dan pencarian ke kiri
Tabel produk di A1:C4:
| Kolom 1 | A | B | C |
|---|---|---|---|
| 1 | Produk | Harga | Stok |
| 2 | Apel | 5000 | 10 |
| 3 | Mangga | 8000 | 5 |
| 4 | Jeruk | 3000 | 20 |
| Rumus | Hasil | Keterangan |
|---|---|---|
=XLOOKUP("Mangga", A2:A4, B2:B4) | 8000 | Cari di A, ambil dari B |
=XLOOKUP("Kiwi", A2:A4, B2:B4, "Tidak ada") | Tidak ada | Pesan sendiri, tanpa IFERROR |
=XLOOKUP(20, C2:C4, A2:A4) | Jeruk | Mencari ke kiri: stok 20 milik siapa |
=XLOOKUP("Jeruk", A2:A4, B2:C4) | 3000 dan 20 | Dua kolom sekaligus (meluber ke sel sebelahnya) |
Untuk tarif bertingkat, tabel batas di E2:F4 (0, 1.000.000, 5.000.000 dan diskon 0%, 5%, 10%): =XLOOKUP(3000000, E2:E4, F2:F4, , -1) menghasilkan 5%.
Kesalahan umum
| Gejala | Penyebab | Perbaikan |
|---|---|---|
#N/A | Nilai tidak ada atau beda tipe ("1001" vs 1001) | Samakan tipe, TRIM, atau isi if_not_found |
#VALUE! | lookup_array dan return_array beda ukuran | Samakan jumlah baris atau kolom |
#NAME? | Versi Excel tidak mengenal XLOOKUP | Pakai INDEX + MATCH |
#SPILL! | Hasil beberapa sel tertahan sel yang terisi | Kosongkan sel di sebelah kanan atau bawahnya |
| Hasil perkiraan salah | match_mode -1 / 1 dipakai pada data yang tidak diurutkan dengan benar | Cek urutan data |
| Rumus ditolak | Pemisah regional Indonesia umumnya titik koma | =XLOOKUP(B6;A2:A4;B2:B4) |
Excel vs Google Sheets, dan VLOOKUP
| Kolom 1 | VLOOKUP | XLOOKUP |
|---|---|---|
| Arah | Hanya ke kanan | Bebas |
| Nomor kolom | Angka tetap, rawan salah saat kolom disisipkan | Rentang langsung |
| Default | Perkiraan (bila argumen ke-4 kosong) | Persis |
| Pesan bila tidak ketemu | Perlu IFERROR | Argumen if_not_found |
| Tersedia di | Semua versi Excel dan Sheets | Excel 2021, 365, web; Google Sheets |
Pakai XLOOKUP bila semua pembuka file memakai Excel 2021/365 atau Google Sheets. Pakai VLOOKUP atau INDEX + MATCH bila file harus terbuka di Excel 2019 atau lebih lama.
Sumber: XLOOKUP di Microsoft Support dan XLOOKUP di Google Docs Editors Help.
Tips
- Tambahkan argumen
if_not_foundsejak awal, agar tampilan tetap rapi tanpa membungkus dengan IFERROR (yang juga menyembunyikan error lain). - Dua syarat sekaligus? Gabungkan kolom di rumus:
=XLOOKUP(A10&B10, A2:A100&B2:B100, C2:C100). - Ambil transaksi terakhir pelanggan: isi
search_modedengan -1 agar pencarian dari bawah.
Lihat juga
Pertanyaan yang sering muncul
Apakah XLOOKUP bisa dipakai di Excel 2016 atau 2019?
Apa kelebihan XLOOKUP dibanding VLOOKUP?
Bisa mengambil data ke kiri, tidak bergantung nomor kolom (aman bila kolom disisipkan), pencocokan persis secara default, punya argumen pesan bila tidak ditemukan, dan bisa mencari dari bawah ke atas.
Bagaimana XLOOKUP untuk tarif bertingkat?
Isi match_mode dengan -1 (cocok persis atau ambil yang lebih kecil terdekat). Contoh: =XLOOKUP(3000000, E2:E4, F2:F4, , -1).
Kenapa XLOOKUP menghasilkan
Nilai tidak ada di lookup_array (cek spasi dan tipe data), atau lookup_array dan return_array beda ukuran. Isi if_not_found bila Anda ingin pesan sendiri.