XLOOKUP di Excel: Sintaks, Contoh, dan Keunggulannya atas VLOOKUP
By Mirko Peters · · Updated
XLOOKUP di Excel dijelaskan: sintaksnya, setiap argumen, contoh untuk pencocokan persis, wildcard, dan pencocokan terakhir, banyak kolom, serta perbedaannya dengan VLOOKUP.
Automatically translated with AI from the original post. Read the English original
XLOOKUP di Excel mencari sebuah nilai dalam satu rentang dan mengembalikan item yang cocok dari rentang lain: =XLOOKUP(lookup_value, lookup_array, return_array). Ketik ID karyawan, arahkan ke kolom ID, arahkan ke kolom nama, dan Anda akan mendapatkan namanya. Artikel ini ditujukan bagi pengguna Excel yang masih menulis VLOOKUP karena kebiasaan, atau yang baru melihat XLOOKUP di file rekan kerja. Anda akan mempelajari sintaksnya, enam argumennya, contoh penerapan untuk kasus yang sering muncul di pekerjaan, perbandingan dengan VLOOKUP, dan daftar kesalahan yang kemungkinan Anda temui.
Poin Penting
- XLOOKUP menerima nilai pencarian, rentang pencarian, dan rentang pengembalian: =XLOOKUP(lookup_value, lookup_array, return_array). Fungsi ini mencari kecocokan pertama dan mengembalikan item dari rentang pengembalian.
- Fungsi ini dapat mencari ke kiri dari kolom pencarian dan defaultnya adalah kecocokan persis, dua hal yang tidak dapat dilakukan VLOOKUP, atau tidak dilakukannya secara default.
- Argumen keempat, if_not_found, menggantikan kesalahan #N/A dengan teks yang Anda pilih. Tanpa argumen ini, pencarian yang tidak cocok akan mengembalikan #N/A.
- match_mode 2 mengaktifkan wildcard, -1 dan 1 mencari item berikutnya yang lebih kecil atau lebih besar, dan search_mode -1 mencari dari item terakhir ke item pertama.
- Halaman Microsoft mencantumkan XLOOKUP untuk Microsoft 365, 2021, dan 2024, sedangkan catatannya tentang Excel 2016 dan 2019 tidak konsisten, sehingga uji file bersama sebelum Anda mengandalkannya.
Sintaks dan opsi di bawah ini mengikuti halaman Microsoft tentang fungsi XLOOKUP. Jika suatu pola tidak ada di halaman tersebut, teks ini akan menyebutkannya. Ini adalah fungsi untuk Excel, dan merupakan fungsi yang disarankan Microsoft sendiri sebagai pilihan yang lebih baik ketika Anda membuka halaman bantuan VLOOKUP.
Sintaks dan keenam argumennya
Rumus lengkapnya terlihat seperti ini:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Tiga argumen pertama wajib diisi. Tiga argumen terakhir bersifat opsional.
lookup_valueadalah nilai yang Anda cari, misalnya ID di sel D2. Jika argumen ini dihilangkan, XLOOKUP akan mengembalikan sel kosong yang ditemukan dalam lookup_array.lookup_arrayadalah rentang atau array yang akan dicari, misalnya A2:A100.return_arrayadalah rentang atau array tempat nilai dikembalikan, misalnya B2:B100.if_not_foundadalah teks yang ditampilkan ketika tidak ada kecocokan yang valid. Jika argumen ini dihilangkan, Anda akan mendapatkan #N/A.match_modemengatur cara pencocokan.0adalah kecocokan persis dan merupakan nilai default.-1berarti kecocokan persis atau, jika tidak ada, item berikutnya yang lebih kecil.1berarti kecocokan persis atau item berikutnya yang lebih besar.2adalah pencocokan wildcard, di mana*,?, dan~memiliki arti khusus.search_modemengatur arah pencarian.1mencari dari item pertama ke item terakhir dan merupakan nilai default.-1mencari dari item terakhir ke item pertama.2dan-2adalah pencarian biner pada data yang diurutkan secara menaik atau menurun. Microsoft memperingatkan bahwa data yang tidak diurutkan akan menghasilkan hasil yang tidak valid pada mode biner.
Pastikan larik pencarian dan larik hasil memiliki ukuran yang sama. Jika larik pencarian mencakup baris 2 sampai 100, larik hasil juga harus mencakup baris 2 sampai 100. Halaman Microsoft tidak menyebutkannya sebagai aturan tertulis, tetapi rentang yang tidak sejajar adalah penyebab umum lookup mengembalikan baris yang salah atau error.
Pencarian dasar dan argumen not-found
Misalkan ID karyawan ada di A2:A100 dan nama ada di B2:B100. Anda mengetikkan ID yang dicari di D2.
- Klik sel tempat hasil akan muncul, misalnya E2.
- Masukkan
=XLOOKUP(D2,A2:A100,B2:B100). - Tekan Enter.
Anda akan mendapatkan nama yang sesuai dengan ID tersebut. XLOOKUP mengembalikan item untuk kecocokan pertama yang ditemukan. Jika ID tidak ada, Anda akan melihat #N/A.
Untuk menampilkan pesan yang lebih ramah, tambahkan argumen keempat:
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
Sekarang ID yang tidak terdaftar akan menampilkan "No record found". Gunakan cara ini pada lembar kerja yang dibaca orang lain. Namun perlu berhati-hati: pesan seperti itu bisa menyembunyikan masalah yang sebenarnya. Jika setiap baris menampilkan "No record found", yang bermasalah adalah rentangnya, bukan datanya, jadi uji dulu dengan ID yang Anda ketahui pasti ada.
Mencari ke kiri
VLOOKUP tidak dapat mengembalikan nilai dari kolom yang berada di sebelah kiri kolom pencarian. XLOOKUP bisa, karena rentang pencarian dan rentang hasil dipisahkan. Jika nama ada di kolom C dan ID yang ingin Anda ambil ada di kolom A:
=XLOOKUP(F2,C2:C100,A2:A100)
Halaman Microsoft menyatakannya secara langsung: kolom pencarian boleh berada di sisi mana pun dari kolom hasil.
Mode pencocokan: wildcard dan nilai terdekat
Pencocokan dengan wildcard
Atur match_mode ke 2 untuk menggunakan wildcard. Tanda bintang mewakili sejumlah karakter apa pun, dan tanda tanya mewakili tepat satu karakter. Tanda tilde memungkinkan Anda mencari tanda bintang atau tanda tanya secara harfiah.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
Rumus ini mengembalikan entri pertama yang diawali dengan "Jan". Jika angka 2 terlewat, Excel mencari teks "Jan*" secara harfiah dan mengembalikan pesan not-found Anda. Ini adalah kesalahan wildcard yang paling sering terjadi. Buatlah wildcard sespesifik mungkin, karena XLOOKUP hanya mengembalikan kecocokan pertama, dan pola yang terlalu longgar bisa cocok dengan sesuatu yang tidak Anda maksudkan.
Item terdekat yang lebih kecil atau lebih besar
Mode -1 dan 1 digunakan untuk skala: tarif pajak, tingkat komisi, titik harga. Letakkan ambang batas di A2:A6 dan tarifnya di B2:B6, lalu cari penghasilan di E2:
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
Dengan -1, Excel mengembalikan kecocokan persis, atau ambang batas yang lebih kecil berikutnya jika tidak ada kecocokan persis. Slot kosong sebelum -1 melewati argumen not-found. Contoh Microsoft untuk kecocokan perkiraan menggunakan 1 untuk mengembalikan tarif pajak bagi nilai penghasilan yang lebih besar berikutnya, jadi pilih arah yang sesuai dengan tabel Anda. Berbeda dengan VLOOKUP perkiraan gaya lama, Anda tidak perlu mengurutkan ambang batas untuk mode ini. Mode pencarian biner adalah yang memerlukan data terurut.
Mencari dari bawah: kecocokan terakhir
Ketika sebuah nilai muncul beberapa kali, XLOOKUP mengembalikan kemunculan pertama yang ditemui. Untuk mendapatkan yang terakhir, cari dari akhir dengan mengatur search_mode ke -1:
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
Jika lembar kerja mencantumkan pesanan menurut urutan tanggal, rumus ini mengembalikan entri terbaru untuk seorang pelanggan. Angka 0 menjaga kecocokan tetap persis. Ini adalah pola untuk pertanyaan seperti "harga terbaru", "kontak terakhir", dan sejenisnya, yang sulit dilakukan dengan VLOOKUP.
Mengembalikan beberapa kolom sekaligus
Halaman Microsoft mencatat bahwa, tidak seperti VLOOKUP, XLOOKUP dapat mengembalikan beberapa kolom sebagai satu larik. Perlebar rentang hasil:
=XLOOKUP(D2,A2:A100,B2:D100)
Rumus ini menempatkan hasil kecocokan dari kolom B di sel tersebut dan menumpahkan nilai dari kolom C dan D ke sel di sebelah kanannya. Sisakan ruang untuk itu. Jika sel di sebelah kanan sudah berisi data, Excel menampilkan error spill alih-alih menimpanya. Contoh Microsoft mengembalikan nama karyawan dan departemen dari satu rumus.
Lookup dua arah
Microsoft menunjukkan cara menyarangkan dua fungsi XLOOKUP untuk menemukan nilai pada perpotongan baris dan kolom. Polanya, dari halaman tersebut, adalah:
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
Fungsi dalam memilih kolom yang cocok dengan header di C3. Fungsi luar kemudian mencari D2 di label baris dan mengembalikan nilai dari kolom tersebut. Microsoft membandingkannya dengan kombinasi INDEX dan MATCH. Jika Anda memiliki laporan dengan produk di sisi kiri dan bulan di bagian atas, ini adalah pengganti untuk rumus yang lebih rumit.
Beberapa kriteria
XLOOKUP menerima satu nilai pencarian, dan halaman Microsoft tidak memiliki contoh untuk beberapa kriteria. Pola yang umum digunakan adalah membiarkan Excel menguji dua kondisi sekaligus. Misalnya, kategori ada di kolom A, status di kolom B, dan harga di kolom C. Untuk mencari harga untuk kategori di F2 dan status di G2:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
Setiap perbandingan menghasilkan TRUE atau FALSE. Perkaliannya menghasilkan 1 hanya pada baris yang kedua kondisinya benar, dan XLOOKUP mencari 1 pertama. Ini adalah pola yang berfungsi di versi Microsoft 365 saat ini, tetapi tidak tercantum di halaman fungsi Microsoft, jadi uji pada sampel kecil terlebih dahulu. Kolom bantu yang menggabungkan kedua bidang adalah alternatif versi lama. Berikan pemisah di antara nilai, misalnya =A2&"|"&B2, lalu cari teks gabungan yang sama.
XLOOKUP dan VLOOKUP berdampingan
Halaman VLOOKUP dari Microsoft menyajikan sintaksnya sebagai VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), dan menyatakan bahwa nilai pencarian harus berada di kolom pertama rentang. Jika Anda menghilangkan range_lookup, VLOOKUP melakukan pencocokan perkiraan, yang mengasumsikan kolom pertama sudah diurutkan. Atur ke FALSE untuk pencocokan persis. Halaman yang sama juga menyarankan untuk mencoba XLOOKUP dan menyebutkan bahwa fungsi ini bekerja ke segala arah serta mengembalikan kecocokan persis secara default.
Tugas yang sama dengan kedua fungsi, dengan ID di kolom A dan nama di kolom B:
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
Perbedaan yang penting dalam penggunaan sehari-hari:
- VLOOKUP merujuk hasil berdasarkan nomor kolom, sehingga menyisipkan kolom dalam tabel akan mengubah arti angka tersebut. XLOOKUP merujuk langsung ke rentang hasil.
- VLOOKUP tidak dapat mencari ke kiri. XLOOKUP bisa.
- VLOOKUP secara default menggunakan pencocokan perkiraan. XLOOKUP secara default menggunakan pencocokan persis.
- XLOOKUP memiliki argumen untuk kondisi tidak ditemukan dan arah pencarian yang sudah termasuk di dalamnya.
Anda tidak harus menulis ulang buku kerja lama. Jika VLOOKUP sudah berfungsi, biarkan saja. Gunakan XLOOKUP untuk rumus baru, dan untuk pencarian yang memerlukan pencarian ke kiri, kecocokan terakhir, atau beberapa kolom hasil.
Versi Excel mana yang mendukung XLOOKUP
Di sinilah Anda perlu berhati-hati. Halaman Microsoft mencantumkan XLOOKUP untuk Excel untuk Microsoft 365 di Windows dan Mac, Excel 2024, Excel 2021, serta aplikasi iPad, iPhone, dan Android. Sebuah catatan di halaman tersebut menyebutkan bahwa fungsi ini tidak tersedia di Excel 2016 atau 2019, tetapi mungkin muncul di buku kerja yang dibuat di versi yang lebih baru. Namun, halaman yang sama juga mencantumkan 2016 dan 2019 pada baris "berlaku untuk". Informasinya saling bertentangan, jadi jangan anggap Excel 2016 dan 2019 aman. Excel untuk web juga tidak tercantum di halaman tersebut.
Jika Anda mengirim buku kerja ke seseorang yang menggunakan versi lama, rumus mungkin menampilkan kesalahan di tempat yang seharusnya menampilkan nilai. Tanyakan versi apa yang digunakan penerima Anda, dan untuk file yang dikirim ke banyak orang, uji pada versi tertua yang masih digunakan. Jika Anda tidak bisa menguji, gunakan fungsi pencarian klasik untuk file tersebut.
Kesalahan dan kekeliruan umum
- Lupa mengatur
match_modeke 2 ketika menggunakan wildcard. Pencarian kemudian mencari karakter secara harfiah. - Menggunakan
search_modebiner (2 atau -2) pada data yang belum diurutkan. Microsoft menyatakan bahwa hasilnya tidak valid. - Rentang pencarian dan rentang hasil dengan ukuran berbeda. Pastikan keduanya memiliki panjang yang sama.
- Spasi tersembunyi dan angka yang disimpan sebagai teks. "100" dan 100 berbeda, sehingga pencarian gagal dan Anda melihat #N/A. Bersihkan data dengan TRIM, atau ubah angkanya.
- Menyembunyikan masalah dengan pesan tidak ditemukan yang ramah. Uji satu nilai yang sudah diketahui sebelum Anda mengandalkannya.
- Mengarahkan array hasil ke sel yang harus menampilkan hasil tumpahan, dan membiarkan sel tersebut terisi.
- Referensi kolom penuh pada lembar kerja yang sangat besar. Gunakan rentang yang terbatas atau Tabel Excel, sehingga Excel memproses lebih sedikit sel.
Topik terkait untuk dieksplorasi
- XLOOKUP versus INDEX dan MATCH. Keduanya dapat melakukan pencarian ke kiri, dan pasangan INDEX dan MATCH juga berfungsi di versi Excel yang lebih lama. Pelajari lebih lanjut jika Anda berbagi file dengan pengguna Excel 2016 atau 2019.
- Beberapa hasil. XLOOKUP hanya mengembalikan kecocokan pertama. Untuk mendapatkan semua kecocokan, gunakan fungsi FILTER.
- XLOOKUP dengan Tabel Excel. Referensi terstruktur membuat rentang ikut bertambah seiring data, dan formulanya mudah dibaca.
- Data dari Power Query. Setelah Anda memuat data ke dalam tabel, XLOOKUP dapat mencarinya seperti tabel lainnya. Segarkan kueri terlebih dahulu, lalu periksa hasilnya.
- Excel untuk web. Halaman XLOOKUP dari Microsoft tidak mencantumkannya, jadi periksa di lingkungan Anda sendiri apakah fungsi ini berfungsi bagi rekan kerja Anda sebelum Anda membagikan file.
Langkah berikutnya
Ambil satu VLOOKUP dari buku kerja Anda saat ini dan tulis ulang sebagai XLOOKUP. Tambahkan pesan jika data tidak ditemukan, lalu bandingkan kedua hasil pada beberapa ID yang Anda kenal. Jika keduanya sama, Anda sudah memiliki pola yang dapat digunakan kembali untuk setiap XLOOKUP baru di Excel.