Dalam pilihan XLOOKUP vs VLOOKUP, pakai XLOOKUP jika semua orang yang membuka file memakai Excel untuk Microsoft 365, Excel 2021 atau yang lebih baru, atau Excel untuk web. XLOOKUP melakukan semua yang dilakukan VLOOKUP dengan lebih sedikit kesalahan: pencocokan persis secara bawaan, bisa mencari ke kiri, dan tidak rusak saat Anda menyisipkan kolom. Pakai VLOOKUP hanya jika buku kerja harus berfungsi di Excel 2019 atau yang lebih lama.
Soal kompatibilitas itulah catatan utamanya. XLOOKUP tidak ada di Excel 2016 atau 2019, sehingga file yang penuh rumus XLOOKUP menampilkan error #NAME? bagi siapa pun yang masih memakai versi tersebut.
Rumus dalam artikel ini ditampilkan seperti di Excel versi bahasa Inggris, dengan koma sebagai pemisah argumen. Jika Excel Anda memakai pengaturan regional Indonesia dan rumus gagal diterima, ganti koma (,) dengan titik koma (;). Nama fungsi VLOOKUP dan XLOOKUP sama persis di semua bahasa, jadi Anda bisa mengetiknya apa adanya.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Mencari ke kiri (mengembalikan kolom di kiri kolom pencarian) | Tidak | Ya |
| Jenis pencocokan bawaan | Perkiraan (kecuali Anda menambahkan FALSE) | Persis |
| Tahan terhadap penyisipan atau penghapusan kolom | Tidak, nomor kolom tetap | Ya, rentang menyesuaikan |
| Pesan "tidak ditemukan" kustom | Perlu IFNA atau IFERROR | Bawaan (if_not_found) |
| Mengembalikan beberapa kolom sekaligus | Tidak mudah | Ya, hasilnya tumpah (spill) |
| Mencari dari kecocokan terakhir ke atas | Tidak | Ya (search_mode -1) |
| Pencocokan perkiraan | Data harus terurut menaik | Berikutnya yang lebih kecil atau lebih besar, tanpa pengurutan |
Wildcard (*, ?) | Ya, dengan pencocokan persis | Ya, dengan match_mode 2 |
| Pencarian horizontal | Tidak, itu HLOOKUP | Ya, fungsi yang sama |
| Kecepatan | Tidak ada beda praktis pada buku kerja umumnya | Sama, plus pencarian biner opsional pada data terurut |
| Tersedia di | Semua versi Excel | Microsoft 365, Excel 2021, Excel 2024, Excel untuk web, aplikasi seluler |
Sintaks berdampingan
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Perbedaan besarnya ada pada cara Anda memberi tahu Excel di mana jawabannya berada. VLOOKUP menerima satu tabel utuh dan nomor kolom yang dihitung dari tepi kiri tabel itu. XLOOKUP menerima dua rentang terpisah: kolom tempat mencari dan kolom tempat mengambil hasil.
Pencarian yang sama ditulis dengan dua cara
Berikut daftar produk kecil di A1:D6, dengan ID produk diketik di G2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P-101 | Wireless Mouse | Accessories | $24.99 |
| 3 | P-102 | USB-C Hub | Accessories | $39.50 |
| 4 | P-103 | Laptop Stand | Office | $45.00 |
| 5 | P-104 | Webcam HD | Video | $59.00 |
| 6 | P-105 | Desk Lamp | Office | $32.75 |
Untuk menemukan harga P-104:
=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)
Keduanya mengembalikan $59.00. VLOOKUP mencari di kolom pertama A2:D6 dan mengembalikan kolom ke-4; FALSE memaksa pencocokan persis. XLOOKUP mencari di A2:A6 dan mengembalikan sel yang cocok dari D2:D6, dengan pencocokan persis sebagai bawaannya.
Jika Anda akan menyalin rumus ke bawah satu kolom, kunci tabelnya dengan referensi absolut, misalnya =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6). Tekan F4 di Windows atau Cmd+T di Mac saat kursor berada di dalam referensi untuk menambahkan tanda $.
Dua jebakan klasik VLOOKUP
1. Lupa FALSE (atau 0)
Argumen terakhir VLOOKUP bersifat opsional, dan jika Anda menghilangkannya Excel memakai pencocokan perkiraan. Dengan pencocokan perkiraan, VLOOKUP berasumsi bahwa kolom pertama terurut dan mengembalikan nilai terdekat yang kurang dari atau sama dengan yang Anda cari, tanpa peringatan apa pun.
Coba =VLOOKUP("P-110", A2:D6, 4) pada tabel di atas. P-110 tidak ada, tetapi alih-alih #N/A Anda mendapat $32.75, harga P-105, karena "P-105" adalah nilai lebih kecil yang paling dekat. Pada data yang tidak terurut, hasilnya bisa lebih acak lagi.
Selalu akhiri VLOOKUP pencocokan persis dengan FALSE atau 0. XLOOKUP menghindari masalah ini karena memakai pencocokan persis kecuali Anda meminta yang lain.
2. Nomor kolom yang ditulis mati
Angka 4 di rumus VLOOKUP hanyalah angka biasa. Jika seseorang menyisipkan kolom Supplier di antara Product dan Category, tabel menjadi A:E, Price pindah ke kolom E, dan rumus tetap mengembalikan kolom ke-4, yang sekarang adalah Category. Anda akan melihat "Video" alih-alih $59.00.
XLOOKUP menunjuk langsung ke kolom harga (D2:D6), sehingga Excel memperbaruinya menjadi E2:E6 secara otomatis dan jawabannya tetap benar.
Yang bisa dilakukan XLOOKUP tetapi tidak bisa dilakukan VLOOKUP
Mencari ke kiri
VLOOKUP hanya bisa mengembalikan kolom di sebelah kanan kolom pencarian. XLOOKUP tidak peduli arah. Untuk menemukan ID produk dari nama produk di G2:
=XLOOKUP(G2, B2:B6, A2:A6)
Dengan "Desk Lamp" di G2, rumus ini mengembalikan P-105, padahal kolom A berada di sebelah kiri kolom B.
Menampilkan pesan ramah saat tidak ada yang ditemukan
=XLOOKUP(G2, A2:A6, D2:D6, "Not found")
Padanan VLOOKUP-nya memerlukan pembungkus: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found").
Mengembalikan beberapa kolom dalam satu rumus
=XLOOKUP(G2, A2:A6, B2:D6)
Untuk P-104, rumus ini menumpahkan Webcam HD, Video, dan $59.00 ke tiga sel yang berdampingan. Biarkan sel-sel itu kosong, atau Anda akan mendapat error #SPILL!.
Menemukan kecocokan terakhir, bukan yang pertama
Pada log di mana pelanggan yang sama muncul berkali-kali, atur search_mode ke -1 untuk mencari dari bawah ke atas dan mendapatkan entri terbaru:
=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)
Pencocokan perkiraan tanpa pengurutan
Untuk harga bertingkat atau tarif komisi, pencocokan perkiraan VLOOKUP hanya berfungsi jika kolom pertama terurut menaik. match_mode -1 XLOOKUP (persis, atau item yang lebih kecil berikutnya) dan 1 (persis, atau item yang lebih besar berikutnya) tidak memerlukan data terurut. Dengan jumlah pesanan minimum di J2:J5 dan diskon di K2:K5:
=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)
Wildcard
Keduanya mendukung * dan ?, tetapi XLOOKUP memerlukan match_mode 2 agar memperlakukannya sebagai wildcard:
=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)
Keduanya mengembalikan $32.75 untuk Desk Lamp.
INDEX/MATCH: alternatif klasik
Sebelum XLOOKUP, perbaikan standar untuk keterbatasan VLOOKUP adalah INDEX dengan MATCH. Kombinasi ini bisa mencari ke kiri, tahan terhadap kolom yang disisipkan, dan berfungsi di semua versi Excel, sehingga menjadi pilihan terbaik untuk file yang harus terbuka di Excel 2019 atau yang lebih lama:
=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))
Rumus pertama mengembalikan harga untuk ID di G2; yang kedua adalah contoh mencari ke kiri. Angka 0 di MATCH berarti pencocokan persis, dan seperti FALSE pada VLOOKUP, mudah terlupa. XLOOKUP pada dasarnya adalah versi yang lebih ringkas dan lebih aman dari gagasan yang sama.
Cara mengonversi rumus VLOOKUP ke XLOOKUP
Tidak ada konverter bawaan, tetapi penerjemahannya bersifat mekanis:
- Temukan VLOOKUP Anda: tekan
Ctrl+F(Cmd+Fdi Mac), ketik VLOOKUP, klik Opsi jika pengaturan tambahan tersembunyi, atur Lihat di (Look in) ke Rumus (Formulas), lalu klik Temukan Semua (Find All). - Biarkan argumen pertama (nilai pencarian) apa adanya.
- Ganti tabel dengan kolom pertamanya sebagai
lookup_array. - Ganti nomor kolom dengan kolom yang sesuai sebagai
return_array. Kolom 5 dari tabel yang dimulai di kolom A adalah kolom E. - Hapus
FALSE. Jika rumus asli diakhiriTRUEatau tidak punya argumen terakhir, tambahkanmatch_mode-1 untuk mempertahankan perilaku "yang lebih kecil berikutnya".
Contohnya:
=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)
Uji beberapa hasil terhadap rumus lama sebelum mengonversi satu lembar penuh. VLOOKUP pencocokan perkiraan pada data tidak terurut mungkin selama ini mengembalikan nilai yang salah, sehingga jawaban XLOOKUP bisa saja berbeda secara wajar.
Apa yang terjadi saat file XLOOKUP dibuka di Excel 2016 atau 2019
Buku kerja tetap terbuka, tetapi versi tersebut tidak mengenal fungsinya. Di bilah rumus, fungsi itu tampil sebagai =_xlfn.XLOOKUP(...). Sel mungkin awalnya masih menampilkan hasil tersimpan terakhir, tetapi begitu rumus dihitung ulang, hasilnya #NAME?.
Jika Anda berbagi file dengan orang yang memakai versi lama:
- Gunakan INDEX/MATCH atau VLOOKUP, atau
- Kirim salinan dengan hasil yang ditempel sebagai nilai (Salin → Tempel Spesial → Nilai).
Hal yang sama berlaku untuk fungsi baru lainnya seperti FILTER, UNIQUE, dan XMATCH.
XLOOKUP di Google Sheets
Google Sheets juga punya XLOOKUP, dengan urutan argumen yang sama: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). Rumus dalam artikel ini berfungsi di sana apa adanya, jadi XLOOKUP adalah pilihan aman untuk file yang berpindah antara Excel dan Sheets.
Mengapa pencarian Anda menghasilkan #N/A atau nilai yang salah
- Spasi berlebih. "P-104 " dengan spasi di akhir tidak cocok dengan "P-104". Bersihkan data dengan
TRIM. - Angka yang tersimpan sebagai teks. ID yang diketik sebagai teks tidak akan cocok dengan ID yang sama yang tersimpan sebagai angka. Cari segitiga hijau di sudut sel lalu ubah kolom menjadi satu jenis yang sama.
- FALSE tidak ada di VLOOKUP. Anda mendapat jawaban yang tampak masuk akal tetapi salah, bukan
#N/A. - Rentang yang bergeser saat rumus disalin. Gunakan referensi absolut
$untuk tabel pencarian. #REF!dari VLOOKUP. Nomor kolom lebih besar daripada jumlah kolom di tabel.#VALUE!dari XLOOKUP. Rentang pencarian dan rentang hasil berukuran berbeda, misalnya A2:A6 dan D2:D60.#NAME?Nama fungsi salah ketik, atau file dibuka di versi tanpa XLOOKUP.
Pertanyaan yang Sering Diajukan
Apakah XLOOKUP lebih baik daripada VLOOKUP? Ya, untuk hampir semua penggunaan. XLOOKUP memakai pencocokan persis secara bawaan, mencari ke arah mana pun, tidak rusak saat kolom berpindah, dan menangani nilai yang tidak ditemukan tanpa pembungkus. Satu-satunya keunggulan nyata VLOOKUP adalah berfungsi di semua versi Excel.
Apakah XLOOKUP lebih cepat daripada VLOOKUP? Pada buku kerja sehari-hari Anda tidak akan melihat perbedaan. Pada daftar terurut yang sangat besar, mode pencarian biner XLOOKUP (search_mode 2 atau -2) bisa mempercepat, tetapi hasilnya salah jika data tidak terurut.
Mengapa XLOOKUP menampilkan #NAME? di komputer saya? Versi Excel Anda tidak menyertakan XLOOKUP. Fungsi ini ada di Microsoft 365, Excel 2021 dan yang lebih baru, serta Excel untuk web, tetapi tidak di Excel 2019 atau 2016. Gunakan INDEX/MATCH sebagai gantinya.
Apakah XLOOKUP juga menggantikan HLOOKUP? Ya. Jika rentang pencarian dan rentang hasil berupa baris, bukan kolom, XLOOKUP mencari secara horizontal dengan sintaks yang sama.
Bisakah XLOOKUP mengembalikan semua kecocokan, bukan hanya yang pertama? Tidak, XLOOKUP mengembalikan satu kecocokan (atau satu baris kolom). Untuk mendapatkan setiap baris yang cocok, gunakan FILTER, misalnya =FILTER(A2:D6, C2:C6="Office").
Apakah saya masih perlu belajar VLOOKUP? Layak untuk dikenali, karena Anda akan menemukannya di buku kerja dan templat lama. Untuk rumus baru, pelajari XLOOKUP, dan ingat INDEX/MATCH untuk file yang harus terbuka di Excel versi lama.
Biarkan AI menulis rumus pencarian untuk Anda
Rumus pencarian adalah tempat detail kecil, seperti FALSE yang hilang atau rentang yang bergeser, menimbulkan paling banyak error yang diam-diam. CoreGPT for Excel memungkinkan Anda mendeskripsikan kebutuhan, seperti "temukan harga untuk ID produk di G2" atau "tandai setiap baris sebagai di atas atau di bawah target", lalu menuliskan rumusnya ke lembar Anda disertai penjelasan singkat. Gratis untuk dicoba dan berfungsi di Excel untuk Windows, Mac, dan web.
CoreGPTPakai rumus ini lalu isi ke bawah:
=IF(C2>=D2,"Di atas","Di bawah")Coba sekarang: CoreGPT for Excel
Bacaan terkait:
