XLOOKUP(): Fungsi Pencarian Paling Serbaguna di Excel

Jika Anda pernah menggunakan VLOOKUP() dan terhambat karena perlu mencari ke kiri, XLOOKUP() menyelesaikan masalah tersebut. Ini adalah fungsi pencarian terbaru di Excel. Fungsi ini dapat menggantikan VLOOKUP(), HLOOKUP(), dan bahkan sebagian besar rumus INDEX() dan MATCH() hanya dengan satu rumus sederhana.

XLOOKUP() dapat mencari ke segala arah, menarik lebih dari satu kolom sekaligus, dan menampilkan pesan khusus saat suatu nilai tidak ditemukan. 

Dalam panduan ini, kita akan membahas sintaks terlebih dahulu. Lalu kita akan membangun rumus nyata selangkah demi selangkah, menggunakan satu set data sederhana dari awal hingga akhir. Kita juga akan membahas kecocokan tepat dan perkiraan, cara mengembalikan lebih dari satu kolom, cara menangani error, dan bagaimana XLOOKUP() dibandingkan dengan VLOOKUP() serta INDEX()/MATCH(). 

Cara Menggunakan XLOOKUP() di Excel

Mari kita membangun rumus XLOOKUP() yang nyata dari nol. 

Dataset kita memiliki lima produk, masing-masing dengan ID, nama, kategori, dan harga, dari kolom A sampai D, dengan header di baris 1.

Dataset untuk XLOOKUP(). Gambar oleh Penulis.

Misalkan kita ingin mencari harga "P104." Mari kita lihat langkah demi langkah cara melakukannya.

Mencari satu nilai

Klik sel kosong tempat Anda ingin hasilnya muncul. Saya menggunakan F2. Lalu ketik rumus berikut:

=XLOOKUP("P104", A2:A6, D2:D6)

Dalam rumus ini: 

• "P104" adalah lookup_value, hal yang kita cari

• A2:A6 adalah lookup_array, rentang tempat Excel melakukan pencarian

• D2:D6 adalah return_array, rentang tempat Excel mengambil jawaban

Excel mencari di kolom A, “Product ID,” untuk "P104," menemukannya di baris 5, dan mengembalikan nilai yang sesuai dari kolom D: $45.

Alih-alih langsung mengetik "P104", Anda bisa mereferensikan sebuah sel, sehingga Anda dapat mengubah nilai pencarian tanpa mengedit rumusnya. Dalam contoh kita, sel A5 berisi "P104," jadi rumusnya menjadi:

=XLOOKUP(A5, A2:A6, D2:D6)

Gunakan XLOOKUP() di Excel. Gambar oleh Penulis.

Sekarang Anda bisa mengetik ID produk apa pun ke A5, dan harga akan diperbarui secara otomatis.

Mengembalikan nilai dari kolom lain

Langkah ini menunjukkan perbedaan nyata antara XLOOKUP() dan VLOOKUP(). Anda tidak terkunci untuk hanya mengembalikan nilai dari kolom di kanan kolom pencarian.

Misalkan alih-alih harga, Anda ingin kategori untuk "P102." Pengaturan sama, cukup arahkan return_array ke kolom C, “Category” sebagai gantinya:

=XLOOKUP(A3, A2:A6, C2:C6)

Di sini, A3 mengembalikan Home. Jika diubah ke A5, akan diperbarui menjadi Electronics. Demikian pula, ubah lagi return_array untuk menarik nama produk, harga, atau kolom lain mana pun, semuanya menggunakan lookup_value dan lookup_array yang sama.

XLOOKUP() mengembalikan nilai dari kolom lain. Gambar oleh Penulis.

Dengan VLOOKUP(), Anda harus menghitung jumlah kolom dari kolom pencarian dan menyesuaikan nomor indeks kolom setiap kali menginginkan hasil berbeda. Dengan XLOOKUP(), Anda cukup menunjuk langsung ke kolom yang diinginkan. 

Menyalin rumus ke banyak baris

Sejauh ini, kita mencari satu nilai pada satu waktu. Namun kebanyakan spreadsheet nyata perlu mencari banyak nilai sekaligus, misalnya daftar lengkap ID pesanan yang masing-masing memerlukan harga yang cocok.

Berikut cara menyiapkannya: Kolom A, mulai dari A2, sudah berisi daftar Product ID. Kita ingin harga yang cocok muncul di sebelah masing-masing, di kolom F.

Untuk melakukannya, klik F2 dan ketik rumusnya, mereferensikan A2 untuk nilai pencarian seperti ini:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Sekarang klik lagi F2, lalu seret fill handle (kotak kecil di pojok kanan bawah sel) ke bawah melewati sisa baris Anda.

Excel menyalin rumusnya ke bawah, dan setiap baris kini mengembalikan harga yang benar untuk Product ID miliknya. ID di A2 mendapat harganya di F2, ID di A3 mendapat harganya di F3, dan seterusnya.

Salin rumus ke sel lain. Gambar oleh Penulis.

Perhatikan tanda dolar di sekitar A2:A6 dan D2:D6. Ini disebut referensi absolut. Ini mengunci rentang agar tidak bergeser saat Anda menyalin rumus ke bawah. 

Hal ini penting di sini, karena setiap baris perlu mencari di daftar produk yang sama, hanya dengan nilai pencarian yang berbeda setiap kali.

Cara Menggunakan Exact Match dan Approximate Match di XLOOKUP()

Secara default, XLOOKUP() mencari kecocokan tepat. Jika tidak menemukannya, fungsi ini mengembalikan error, kecuali Anda telah menyiapkan argumen if_not_found yang telah kita bahas sebelumnya. 

Namun XLOOKUP() juga bisa mencari kecocokan terdekat, menggunakan argumen match_mode. 

Exact match (Default)

Anda tidak perlu mengetik apa pun tambahan untuk ini. Setiap rumus yang telah kita buat sejauh ini sudah menggunakan kecocokan tepat, karena itu adalah perilaku default XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel mencari nilai persis di A2, dan hanya mengembalikan hasil jika menemukan kecocokan tepat tersebut di suatu tempat dalam rentang pencarian.

Jika Anda ingin menuliskannya secara eksplisit, tambahkan nol sebagai argumen keempat:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() mengembalikan kecocokan tepat. Gambar oleh Penulis.

Perhatikan spasi sebelum angka 0? Itu melewati if_not_found, karena kita membiarkannya kosong di sini, dan langsung menuju match_mode. Sebagian besar waktu, Anda bisa mengabaikan argumen ini sepenuhnya dan membiarkan Excel default ke kecocokan tepat secara otomatis.

Approximate match

Sekarang, misalkan data Anda tidak memiliki nilai untuk setiap kemungkinan pencarian. Pikirkan struktur komisi di mana sales memperoleh tarif berbeda tergantung pada rentang total penjualannya. Anda tidak akan memiliki baris untuk setiap jumlah dolar, hanya titik awal setiap rentang.

Di sinilah approximate match membantu. 

Atur match_mode ke -1, dan XLOOKUP() akan menemukan nilai terbesar yang kurang dari atau sama dengan nilai pencarian Anda.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Misalkan rentang pencarian Anda menyimpan ambang penjualan $0, $5.000, dan $10.000, masing-masing terkait dengan tarif komisi yang berbeda. 

Seorang sales dengan penjualan $7.500 tidak cocok dengan baris mana pun secara tepat. Dengan match_mode disetel ke -1, XLOOKUP() menemukan $5.000, ambang terbesar yang tidak melebihi $7.500, dan mengembalikan tarif komisi baris tersebut.

Atur match_mode ke 1, dan XLOOKUP() membalik logikanya. Fungsi ini mencari nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian Anda. Ini berguna untuk hal-hal seperti batas berat pengiriman, di mana Anda membulatkan ke atas ke tingkat berikutnya, bukan ke bawah.

XLOOKUP() mengembalikan kecocokan perkiraan. Gambar oleh Penulis.

Catatan: approximate match mengasumsikan rentang pencarian Anda terurut, naik untuk -1, turun untuk 1. Jika tidak diurutkan dengan benar, XLOOKUP() bisa mengembalikan hasil yang salah tanpa menampilkan error, yang lebih buruk. Jadi, selalu periksa ulang urutan sortir Anda sebelum mengandalkannya.

Cara Mencari Nilai ke Kiri dengan XLOOKUP()

VLOOKUP() hanya mencari kolom paling kiri dari rentang Anda dan mengembalikan nilai dari kolom di kanannya; fungsi ini tidak bisa sebaliknya. Argumen ketiganya bukan rentang sama sekali; melainkan nomor indeks kolom yang dihitung dari tepi kiri. 

Tidak ada cara untuk memerintahkannya “maju satu kolom ke kiri,” jadi jika nilai yang Anda butuhkan berada di kiri kolom pencarian, Anda terpaksa menata ulang data atau menambahkan kolom bantu hanya agar pencarian dapat bekerja.

XLOOKUP() tidak memiliki batasan ini, karena Anda menunjukkannya ke rentang yang terpisah untuk pencarian dan pengembalian.

Ambil daftar produk kita: Product ID di kolom A, Product Name di kolom B. 

Misalkan Anda memiliki nama produk dan ingin mencari ID-nya; kebalikan dari yang bisa dilakukan VLOOKUP().

Di C9, masukkan Bluetooth Speaker (nama yang ada di daftar). Di E2, masukkan:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() mencari di kolom B untuk "Bluetooth Speaker," menemukannya di baris 5, dan mengembalikan "P104" dari kolom A, meskipun kolom A berada di sebelah kiri kolom B. 

XLOOKUP() dapat mencari nilai ke kiri, tidak seperti VLOOKUP(). Gambar oleh Penulis.

Cara Mengembalikan Banyak Kolom dengan XLOOKUP()

XLOOKUP() dapat mengembalikan beberapa kolom sekaligus, dalam satu rumus, menggunakan fitur yang disebut dynamic arrays. Caranya, arahkan return_array ke sebuah rentang alih-alih satu kolom, dan XLOOKUP() akan menumpahkan seluruh baris yang cocok ke sebanyak sel yang diperlukan. 

Mari kita coba dengan daftar produk kita. 

Di C9, saya mengetik sebuah Product ID, misalnya, "P104." Di E2 saya masukkan:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Perhatikan return_array di sini adalah B2:D6, selebar tiga kolom, mencakup Product Name, Category, dan Price. Setelah Anda menekan Enter, XLOOKUP() menemukan "P104" di kolom A, lalu sekaligus mengembalikan Bluetooth Speaker, Electronics, dan $45, menumpah ke E9, F9, dan G9.

XLOOKUP() mengembalikan banyak kolom dengan satu rumus. Gambar oleh Penulis.

Inilah yang dimaksud dengan spill range. E9 memiliki rumus sebenarnya, dan F9 serta G9 terisi sendiri, tanpa rumusnya masing-masing. 

Klik F9, dan bilah rumus menampilkan versi rumus asli yang dipudarkan, bukan sesuatu yang diketik langsung di sana. Coba ketik ke F9 saat spill dari E9 aktif, dan Excel akan menampilkan error spill. 

Kita akan membahas cara memperbaikinya di bagian Error Umum.

Cara Menangani Nilai yang Hilang di XLOOKUP()

Secara default, XLOOKUP() menampilkan error #N/A saat tidak dapat menemukan kecocokan. Argumen if_not_found dapat memperbaikinya dengan menampilkan apa yang kita inginkan alih-alih error default.  

Misalkan di C9, saya mengetik Product ID yang tidak ada di data saya, seperti "P999."

Mengembalikan pesan khusus

Di G2, saya masukkan:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Karena "P999" tidak ada di kolom A, XLOOKUP() melewati error sama sekali dan mengembalikan "Not Found". Ubah frasa sesuai keinginan, seperti "Check Product ID" atau "No Match".

Tampilkan pesan khusus alih-alih error default. Gambar oleh Penulis.

Mengembalikan sel kosong

Terkadang Anda menginginkan sel kosong tanpa teks. Untuk ini, gunakan dua tanda kutip tanpa apa pun di antaranya, seperti ini:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Anda bisa menggunakan ini saat menggunakan rumus dalam laporan di mana "Not Found" yang terselip akan terlihat berantakan di sebelah kolom nilai sebenarnya.

Tampilkan sel kosong alih-alih error default. Gambar oleh Penulis.

Membiarkannya sebagai error yang sebenarnya

Jika Anda melewati if_not_found sepenuhnya, XLOOKUP() akan kembali ke #N/A. Ini tidak selalu buruk. Jika Anda membangun rumus untuk penggunaan sendiri dan ingin segera menangkap salah ketik atau entri yang hilang, melihat #N/A muncul lebih membantu daripada sel kosong yang diam-diam menyembunyikan masalah.

Catatan: if_not_found hanya menangkap kecocokan yang hilang. Jika lookup_array dan return_array berukuran berbeda, misalnya, XLOOKUP() tetap akan menampilkan error #VALUE!, dan if_not_found tidak akan menghentikan yang satu itu. Kita akan membahas kasus spesifik ini di bagian Error Umum lebih jauh ke bawah.

Fitur Lanjutan XLOOKUP()

Mari kita lihat fitur-fitur lanjutan dari XLOOKUP()

Mencari dari bawah ke atas

Atur search_mode ke -1, dan XLOOKUP() memindai dari bagian bawah rentang Anda ke atas, bukan dari atas ke bawah. Gunakan ini ketika data Anda memiliki nilai duplikat, dan Anda menginginkan entri yang paling baru, bukan yang pertama.

Misalkan di dataset saya ada tiga Customer ID yang duplikat. Di sel E2, saya memasukkan rumus default yang akan menggunakan pendekatan dari atas ke bawah: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Ini dihitung dari awal tabel dan menampilkan hasil dari kecocokan pertama. 

Namun jika saya ingin mencari dari bawah, saya akan menggunakan rumus ini: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Dalam rumus ini, -1 memberi tahu Excel untuk mulai mencari dari bawah dataset. Dan ini akan mengembalikan 340, karena itu adalah nilai kecocokan pertama dari bawah.  

XLOOKUP() memindai data dari bawah ke atas. Gambar oleh Penulis.

Mode pencarian biner

Atur search_mode ke 2, dan XLOOKUP() akan beralih dari memeriksa satu baris pada satu waktu menjadi pencarian biner. 

Begini cara kerjanya: 

• Fungsi melihat item tengah di rentang Anda terlebih dahulu. 

• Jika itu bukan kecocokan, ia membuang setengah bagian di mana nilainya tidak mungkin berada, lalu melihat bagian tengah dari sisa data. 

• Fungsi terus melakukannya, membagi data menjadi dua setiap kali, hingga menemukan kecocokan. 

• Atur search_mode ke -2, dan fungsinya bekerja dengan cara yang sama, hanya saja mulai dari item terakhir alih-alih yang pertama.

Di sini di E2, masukkan:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Rumus ini malah mengembalikan 200, baris yang sama sekali berbeda.

Mode pencarian biner memberikan hasil yang salah. Gambar oleh Penulis.

Karena pencarian biner hanya berfungsi jika lookup_array Anda terurut. Karena kolom Customer ID kita tidak terurut, urutannya C100, C101, C100, C102, C100, dan pencarian biner membuang setengah yang salah dan memberikan kecocokan yang keliru. 

Sekarang mari lihat apa yang terjadi ketika datanya benar-benar diurutkan. 

Urutkan log yang sama berdasarkan Customer ID, naik (semua C100 di awal) dan jalankan rumus yang sama lagi. 

Mode pencarian biner berfungsi dengan data yang terurut. Gambar oleh Penulis.

Kali ini, E2 mengembalikan 120, sesuai dengan yang akan diberikan oleh pencarian normal. Itulah bedanya. Pencarian biner hanya melewati data yang dapat dipastikan tidak perlu, dan itu hanya berlaku saat data sudah terurut sejak awal. 

Rumus XLOOKUP() bertingkat (nested)

Sarang satu XLOOKUP() di dalam yang lain ketika hasil dari pencarian pertama perlu digunakan untuk pencarian kedua, alih-alih rentang tetap.

Misalnya Anda ingin mencari harga, tetapi kolom mana yang Anda tarik bergantung pada pencarian terpisah, misalnya tingkat harga pelanggan.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

Dalam rumus ini, XLOOKUP() bagian dalam terlebih dahulu menentukan rentang kolom mana yang akan diambil, berdasarkan D9, kemudian XLOOKUP() bagian luar menggunakan hasil tersebut sebagai return_array-nya. 

XLOOKUP() bertingkat. Gambar oleh Penulis.

XLOOKUP() vs. VLOOKUP()

Kita telah membahas cara kerja XLOOKUP() secara detail. Berikut perbandingannya dengan VLOOKUP().

Ada dua hal yang perlu dicatat cepat: 

• VLOOKUP() default ke pencocokan perkiraan. Jika Anda lupa argumen keempat, fungsi ini tidak akan menampilkan error; melainkan diam-diam mengembalikan nilai yang salah.

• Jika Anda menyisipkan kolom ke dalam lembar VLOOKUP(), rumus akan diam-diam mulai menarik nilai yang salah. XLOOKUP() menghindari ini dengan mereferensikan rentang secara langsung, bukan menghitung kolom.

Semua ini tidak membuat VLOOKUP() menjadi usang. Fungsi tersebut masih baik untuk lembar yang sederhana dan statis. Namun untuk apa pun yang dibangun ke depan, XLOOKUP() adalah default yang lebih aman.

XLOOKUP() vs. INDEX() dan MATCH()

Sebelum XLOOKUP() ada, INDEX() dan MATCH() secara bersama adalah cara standar untuk mengatasi keterbatasan VLOOKUP(). Ini menggunakan dua fungsi alih-alih satu. 

• MATCH() menemukan posisi nilai pencarian Anda. 

• INDEX() kemudian menggunakan posisi tersebut untuk menarik hasil dari mana pun Anda menunjuk: kiri atau kanan, tidak masalah.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Bandingkan dengan versi XLOOKUP() yang telah kita gunakan sepanjang artikel ini:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() hanya berfungsi di Microsoft 365 dan Excel 2021 ke atas, seperti yang telah kita bahas. Jika workbook Anda perlu dibuka di Excel 2019 atau 2016, atau Anda berbagi dengan orang yang menggunakan versi lama, INDEX() dan MATCH() masih berfungsi di mana-mana.

Ada juga kasus yang lebih kecil. 

INDEX() dan MATCH() memisahkan “temukan posisinya” dari “kembalikan nilainya,” yang membantu dalam beberapa rumus bertingkat yang kompleks. Ini jarang muncul, jadi sebagian besar pembaca tidak memerlukannya.

Di luar dua kasus ini, XLOOKUP() adalah pilihan yang lebih baik untuk workbook baru. 

Error Umum XLOOKUP() dan Cara Memperbaikinya

Berikut referensi cepat untuk masalah yang paling mungkin Anda temui dan cara memperbaiki masing-masing.

Error #N/A

Ini adalah respons default XLOOKUP() saat tidak dapat menemukan kecocokan. Saya mengalaminya sebelumnya, ketika lookup_value dan lookup_array saya tidak selaras, dan XLOOKUP() tidak memiliki apa pun untuk dicocokkan di kolom A.

Cara memperbaikinya: Periksa ulang bahwa lookup_value Anda benar-benar ada di lookup_array, dieja dan diformat dengan cara yang sama. Jika kecocokan yang hilang adalah hal yang diharapkan dan normal, gunakan argumen if_not_found untuk mengganti #N/A dengan sesuatu yang lebih berguna, seperti "Not Found" atau sel kosong, seperti yang telah kita bahas sebelumnya. 

Rentang pencarian yang salah

Ini terjadi saat lookup_array dan return_array Anda tidak selaras dengan benar: baris yang salah, kolom yang salah, atau rentang yang ukurannya tidak cocok. 

Bergantung pada versi Excel atau Sheets yang Anda gunakan, ini bisa muncul sebagai error #VALUE!, atau terkadang sebagai #N/A dengan pesan terpisah yang menjelaskan masalah sebenarnya. Jangan biarkan #N/A menipu Anda sehingga mengira itu adalah kecocokan yang hilang. 

Cara memperbaikinya: Pastikan lookup_array dan return_array mencakup jumlah baris atau kolom yang sama. Jika lookup_array adalah A2:A5, maka return_array juga harus menjangkau baris 2 hingga 5, bukan baris 4 hingga 5 atau rentang tidak cocok lainnya.

Ketidaksesuaian tipe data

VLOOKUP() mungkin menampilkan error karena mengharapkan angka tetapi mendapat teks. XLOOKUP() dapat mengalami masalah serupa. Jika lookup_value Anda adalah angka, tetapi lookup_array menyimpan nilai yang sama sebagai teks, atau sebaliknya, XLOOKUP() tidak akan menemukan kecocokan, meskipun nilainya tampak identik di layar.

Cara memperbaikinya: Periksa apakah lookup_value dan lookup_array menggunakan tipe data yang sama. Angka yang disimpan sebagai teks biasanya rata kiri di sel, bukan rata kanan. Bungkus lookup_value Anda dalam VALUE() untuk mengonversi teks ke angka, atau gunakan TEXT() untuk melakukan sebaliknya, tergantung arah ketidaksesuaian.

Error spill

Ini terjadi ketika return_array Anda mencakup lebih dari satu kolom, tetapi sel yang dibutuhkan XLOOKUP() tidak kosong. Excel membutuhkan ruang kosong di sebelah sel rumus Anda untuk menumpahkan hasilnya. Jika sudah ada sesuatu di sana, seluruh rumus gagal.

Cara memperbaikinya: Kosongkan sel tempat hasil perlu diletakkan, lalu masukkan rumus Anda. Jika Anda membutuhkan sesuatu di sel-sel itu, pindahkan rumus XLOOKUP() Anda ke tempat lain dengan ruang kosong di sekitarnya.

Penutup 

Sekarang Anda tahu cara menggunakan XLOOKUP() untuk nilai tunggal, banyak kolom, data yang hilang, dan bahkan pencarian bertingkat di dua rentang. Itu sudah mencakup banyak hal.

Ke depan, jadikan XLOOKUP() sebagai default untuk workbook baru. Fungsinya lebih fleksibel daripada VLOOKUP(), lebih mudah dibaca daripada INDEX/MATCH(), dan menyelamatkan Anda dari membungkus setiap rumus dengan IFERROR().

Jika Anda ingin mendalami, ada baiknya mempelajari INDEX() dan MATCH() secara terpisah juga.