Cara menggunakan XLOOKUP di Excel menjadi salah satu keterampilan penting bagi kalian yang sering mengolah data dalam jumlah besar. Fungsi ini membantu pengguna menemukan informasi tertentu berdasarkan nilai pencarian tanpa perlu memeriksa setiap baris secara manual.
Microsoft Excel menyediakan berbagai fungsi pencarian, seperti VLOOKUP, HLOOKUP, INDEX, dan MATCH. Namun, XLOOKUP menawarkan pendekatan yang lebih fleksibel karena mampu mencari data ke berbagai arah, menggunakan pencocokan persis secara otomatis, serta menampilkan beberapa hasil sekaligus.
Sebagai contoh, kalian memiliki daftar ratusan karyawan yang mencakup nomor identitas, nama, divisi, dan gaji. Dengan menggunakan XLOOKUP, kalian cukup memasukkan nomor identitas untuk mendapatkan informasi karyawan yang sesuai.
Selain mempermudah pencarian, fungsi ini juga membantu mengurangi kesalahan ketika kalian menyusun laporan penjualan, mengelola inventaris, atau mencocokkan informasi dari beberapa tabel.
Lantas, bagaimana cara menggunakan rumus XLOOKUP di Excel dengan benar? Berikut pembahasan lengkap beserta contoh yang bisa kalian praktikkan secara langsung.
Apa Itu Fungsi XLOOKUP di Excel?
Fungsi XLOOKUP di Excel merupakan fungsi pencarian dan referensi yang berguna untuk menemukan suatu nilai dalam rentang sel, kemudian mengambil informasi yang berkaitan dengan nilai tersebut dari rentang lainnya.
Secara sederhana, XLOOKUP bekerja dengan mencocokkan nilai yang kalian masukkan dengan data dalam tabel. Ketika menemukan kecocokan, Excel akan mengambil hasil dari posisi yang sesuai pada rentang pengembalian.
Misalnya, sebuah perusahaan menyimpan daftar kode produk pada kolom A dan harga produk pada kolom B. Ketika kalian memasukkan kode produk tertentu, XLOOKUP dapat menemukan harga yang sesuai secara otomatis.
Keunggulan utama XLOOKUP terletak pada fleksibilitas pencariannya. Berbeda dengan VLOOKUP yang memiliki keterbatasan arah pencarian, XLOOKUP dapat mengambil informasi dari kolom sebelah kiri maupun kanan.
Selain itu, kalian tidak perlu menentukan nomor indeks kolom sebagaimana ketika memakai VLOOKUP. Kalian cukup memilih rentang pencarian dan rentang hasil secara langsung.
Dengan kemampuan tersebut, XLOOKUP cocok untuk mengelola database pelanggan, laporan keuangan, informasi akademik, data pegawai, hingga daftar persediaan barang.
Versi Microsoft Excel yang Mendukung XLOOKUP
Versi Microsoft Excel yang mendukung XLOOKUP perlu kalian perhatikan sebelum mencoba fungsi ini. Pasalnya, tidak semua edisi Excel menyediakan fitur tersebut.
XLOOKUP tersedia pada Excel untuk Microsoft 365, Excel 2021, Excel 2024, serta Excel versi web yang mendukung fungsi tersebut. Dengan demikian, pengguna versi modern dapat memanfaatkan XLOOKUP tanpa perlu memasang add-in khusus.
Sementara itu, Excel 2016 dan Excel 2019 tidak menyediakan dukungan XLOOKUP secara bawaan. Microsoft juga menjelaskan bahwa pengguna kedua versi tersebut mungkin menemukan file yang memuat XLOOKUP apabila file tersebut dibuat menggunakan versi Excel yang lebih baru.
Apabila kalian menggunakan Excel versi lama, pertimbangkan kombinasi INDEX dan MATCH atau VLOOKUP sebagai alternatif. Sebaliknya, jika Excel sudah mendukung XLOOKUP, kalian dapat langsung mempraktikkan contoh berikut.
Rumus XLOOKUP di Excel dan Penjelasan Sintaksnya
Rumus XLOOKUP di Excel menggunakan tiga argumen utama dan tiga argumen tambahan yang bersifat opsional. Kalian dapat menyesuaikan setiap argumen berdasarkan jenis pencarian yang dibutuhkan.
Secara umum, sintaks XLOOKUP memiliki bentuk berikut:
=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])Pada contoh artikel ini, rumus menggunakan tanda titik koma (;) sebagai pemisah argumen. Jika pengaturan regional Excel kalian menggunakan tanda koma (,), gantilah setiap titik koma dalam rumus dengan koma.
Berikut penjelasan masing-masing argumennya.
Lookup_value
Lookup_value merupakan nilai yang ingin kalian cari dalam tabel. Nilai tersebut bisa berupa angka, teks, kode produk, nomor identitas, atau referensi sel.
Contohnya, apabila kalian ingin mencari data berdasarkan kode K003 yang tersimpan dalam sel F2, gunakan F2 sebagai lookup_value. Dengan demikian, Excel akan mengikuti nilai yang kalian masukkan pada sel tersebut.
Lookup_array
Lookup_array merupakan rentang sel tempat Excel melakukan pencarian. Rentang ini harus berisi data yang ingin kalian cocokkan dengan lookup_value.
Misalnya, seluruh kode karyawan berada pada A2:A100. Kalian dapat menggunakan rentang tersebut sebagai lookup_array agar Excel mencari kode yang sesuai.
Return_array
Return_array adalah rentang yang berisi informasi yang ingin kalian tampilkan setelah Excel menemukan nilai pencarian.
Sebagai contoh, apabila nama karyawan berada pada B2:B100, kalian dapat memilih rentang tersebut untuk menampilkan nama berdasarkan kode karyawan.
Pastikan jumlah baris pada lookup_array sesuai dengan rentang hasil ketika kalian melakukan pencarian vertikal. Ketidaksesuaian ukuran rentang dapat menyebabkan rumus menghasilkan kesalahan.
If_not_found
If_not_found merupakan argumen opsional untuk menentukan hasil ketika Excel tidak menemukan data yang cocok.
Misalnya, kalian dapat memasukkan teks “Data tidak ditemukan” agar pengguna memperoleh informasi yang lebih mudah dipahami dibandingkan pesan kesalahan #N/A.
Match_mode
Match_mode menentukan metode pencocokan nilai yang digunakan XLOOKUP. Ada empat pilihan yang bisa kalian gunakan, yaitu:
- 0: mencari nilai yang sama persis dan menjadi pengaturan default.
- -1: mencari nilai yang sama persis atau nilai terdekat yang lebih kecil.
- 1: mencari nilai yang sama persis atau nilai terdekat yang lebih besar.
- 2: menggunakan karakter wildcard untuk pencocokan pola teks.
Untuk pencarian kode karyawan, nomor transaksi, atau identitas unik, pencocokan persis biasanya menjadi pilihan yang paling tepat. Sementara itu, pencocokan mendekati lebih cocok untuk menentukan kategori berdasarkan batas nilai.
Search_mode
Search_mode menentukan arah dan metode penelusuran data. Nilai 1 membuat Excel mencari dari awal ke akhir, sedangkan -1 membuat Excel mencari dari akhir ke awal.
Selain itu, nilai 2 menjalankan pencarian biner pada data yang diurutkan secara menaik. Sebaliknya, nilai -2 menjalankan pencarian biner pada data yang diurutkan secara menurun.
Namun, kalian harus memastikan urutan data benar sebelum menggunakan pencarian biner. Apabila data tidak sesuai dengan urutan yang disyaratkan, Excel dapat memberikan hasil yang keliru.
Cara Menggunakan XLOOKUP di Excel untuk Pemula
Cara menggunakan XLOOKUP di Excel untuk pemula dapat kalian pelajari melalui contoh pencarian data karyawan. Metode ini menjadi dasar untuk memahami bagaimana setiap argumen bekerja.
Pertama, buat tabel sederhana pada rentang A1:D6 dengan informasi berikut.
| ID Karyawan | Nama | Divisi | Gaji |
|---|---|---|---|
| K001 | Andi | Keuangan | 6500000 |
| K002 | Bella | Pemasaran | 7000000 |
| K003 | Citra | Operasional | 6200000 |
| K004 | Dimas | TI | 8500000 |
| K005 | Eka | SDM | 6800000 |
Tempatkan ID Karyawan pada kolom A, Nama pada kolom B, Divisi pada kolom C, dan Gaji pada kolom D. Gunakan baris pertama sebagai judul kolom, kemudian isi data pada baris kedua hingga keenam.
Selanjutnya, masukkan kode K003 pada sel F2. Kode tersebut menjadi dasar pencarian untuk menampilkan nama karyawan.
Klik sel G2, kemudian masukkan rumus berikut:
=XLOOKUP(F2;A2:A6;B2:B6)Setelah menekan Enter, Excel akan menampilkan nama Citra. Hasil tersebut muncul karena kode K003 berada pada baris keempat lembar kerja dan berpasangan dengan nama Citra.
Rumus tersebut bekerja melalui tiga proses. Pertama, Excel membaca kode dalam F2. Kedua, Excel mencari kode yang sama pada A2:A6. Ketiga, Excel mengambil nama pada B2:B6 yang menempati posisi bersesuaian.
Jika kalian mengganti isi F2 menjadi K005, hasil pada G2 akan berubah menjadi Eka secara otomatis. Dengan demikian, kalian tidak perlu mengubah rumus setiap kali mencari karyawan lainnya.
Cara Menggunakan XLOOKUP untuk Mencari Data ke Kiri
Cara menggunakan XLOOKUP untuk mencari data ke kiri merupakan salah satu kelebihan penting dibandingkan VLOOKUP. Kalian dapat mengambil informasi dari kolom yang berada di sebelah kiri kolom pencarian tanpa mengubah susunan tabel.
Gunakan kembali tabel karyawan sebelumnya. Kali ini, anggap kalian mengetahui nama karyawan tetapi ingin mencari nomor identitasnya.
Karena nama berada pada kolom B dan ID berada pada kolom A, kalian dapat menggunakan rumus berikut:
=XLOOKUP("Citra";B2:B6;A2:A6)Rumus tersebut menghasilkan K003. Excel mencari nama Citra pada kolom B, kemudian mengambil ID yang sesuai dari kolom A.
Dengan metode ini, kalian tidak perlu memindahkan kolom ID ke sebelah kanan. Kemampuan tersebut sangat membantu ketika kalian mengolah laporan yang memiliki susunan kolom tetap.
Cara Menggunakan XLOOKUP Jika Data Tidak Ditemukan
Cara menggunakan XLOOKUP jika data tidak ditemukan dapat kalian lakukan dengan memanfaatkan argumen if_not_found. Fitur ini berguna untuk membuat hasil pencarian lebih informatif.
Secara default, XLOOKUP menampilkan kesalahan #N/A apabila tidak menemukan nilai yang cocok. Namun, pesan tersebut terkadang membingungkan pengguna yang belum memahami rumus Excel.
Untuk mengatasinya, tambahkan pesan khusus pada argumen keempat seperti berikut:
=XLOOKUP(F2;A2:A6;B2:B6;"ID tidak ditemukan")Apabila F2 berisi K003, Excel tetap menampilkan Citra. Sebaliknya, jika kalian memasukkan K999 yang tidak tersedia dalam tabel, Excel menampilkan pesan “ID tidak ditemukan”.
Penggunaan pesan khusus membuat laporan lebih mudah dibaca. Selain itu, kalian dapat membedakan data yang memang tidak tersedia dengan kesalahan rumus lainnya.
Perlu kalian pahami bahwa if_not_found hanya menangani kondisi ketika pencarian tidak menemukan kecocokan. Argumen ini tidak otomatis menyelesaikan kesalahan akibat susunan rumus atau rentang yang tidak sesuai.
Cara Menggunakan XLOOKUP untuk Menampilkan Beberapa Kolom
Cara menggunakan XLOOKUP untuk menampilkan beberapa kolom memungkinkan kalian mengambil sejumlah informasi sekaligus melalui satu rumus. Kemampuan ini sangat membantu ketika kalian ingin membuat ringkasan data.
Misalnya, kalian ingin menampilkan nama, divisi, dan gaji berdasarkan ID karyawan pada F2. Dengan tabel sebelumnya, gunakan rumus berikut pada sel G2:
=XLOOKUP(F2;A2:A6;B2:D6;"Tidak ditemukan")Jika F2 berisi K003, Excel akan menampilkan Citra pada G2, Operasional pada H2, dan 6200000 pada I2.
Hasil tersebut muncul karena return_array mencakup tiga kolom sekaligus, yaitu B, C, dan D. Excel kemudian menempatkan hasil pada beberapa sel yang bersebelahan melalui mekanisme dynamic array atau spill.
Pastikan sel H2 dan I2 kosong sebelum menjalankan rumus. Apabila terdapat data yang menghalangi area hasil, Excel dapat menampilkan kesalahan #SPILL!.
Dengan fitur ini, kalian tidak perlu membuat tiga rumus terpisah untuk menampilkan informasi karyawan. Selain mempersingkat pekerjaan, penggunaan satu rumus juga membuat pengelolaan data lebih praktis.
Cara Menggunakan XLOOKUP dengan Pencocokan Mendekati
Cara menggunakan XLOOKUP dengan pencocokan mendekati cocok untuk menentukan kategori berdasarkan rentang angka. Contohnya meliputi nilai ujian, kelompok diskon, tingkat komisi, dan klasifikasi penjualan.
Misalnya, kalian mempunyai ketentuan nilai sebagai berikut. Letakkan judul Batas Nilai pada A10 dan Grade pada B10, kemudian isi data pada baris 11 hingga 15.
| Batas Nilai | Grade |
|---|---|
| 0 | E |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
Selanjutnya, masukkan angka 83 pada sel F5 untuk mengetahui grade yang sesuai. Gunakan rumus berikut:
=XLOOKUP(F5;A11:A15;B11:B15;"Nilai di luar rentang";-1)Hasil rumus tersebut adalah B. Alasannya, nilai 83 tidak terdapat secara persis pada daftar batas nilai. Oleh karena itu, Excel menggunakan batas terdekat yang lebih kecil, yaitu 80.
Argumen -1 membuat XLOOKUP mencari kecocokan persis terlebih dahulu. Jika tidak ada, Excel menggunakan nilai berikutnya yang lebih kecil.
Dalam contoh ini, batas nilai sengaja disusun secara menaik agar kalian lebih mudah memahami klasifikasinya. Untuk pencarian biner, urutan data bahkan menjadi persyaratan yang harus dipenuhi.
Sebaliknya, jika kalian menggunakan match_mode bernilai 1, Excel akan mencari nilai persis atau nilai berikutnya yang lebih besar. Pilihan tersebut dapat berguna ketika tabel menggunakan batas maksimum, bukan batas minimum.
Cara Menggunakan XLOOKUP untuk Mencari Data Terakhir
Cara menggunakan XLOOKUP untuk mencari data terakhir berguna ketika sebuah tabel memiliki nilai pencarian yang muncul berulang kali. Situasi ini sering terjadi pada riwayat transaksi, catatan aktivitas, atau daftar perubahan status.
Secara default, XLOOKUP mengambil kecocokan pertama dari atas. Namun, kalian dapat mengubah arah pencarian menggunakan argumen search_mode dengan nilai -1.
Misalnya, kalian memiliki riwayat kode transaksi pada H2:H20 dan status transaksi pada I2:I20. Untuk mengambil status dari kemunculan kode terakhir dalam rentang tersebut, gunakan rumus:
=XLOOKUP(F2;H2:H20;I2:I20;"Tidak ditemukan";0;-1)Pada rumus tersebut, angka 0 menentukan pencocokan persis. Sementara itu, angka -1 membuat Excel memeriksa data dari bagian bawah menuju bagian atas.
Dengan demikian, apabila kode yang sama muncul beberapa kali, XLOOKUP mengambil informasi dari kemunculan terakhir dalam urutan tabel.
Namun, pencarian dari bawah tidak selalu berarti menemukan transaksi dengan tanggal paling baru. Hasil tersebut hanya menunjukkan posisi terakhir dalam rentang. Karena itu, urutkan data berdasarkan waktu terlebih dahulu apabila kalian ingin menampilkan informasi terbaru secara kronologis.
Cara Menggunakan XLOOKUP dengan Karakter Wildcard
Cara menggunakan XLOOKUP dengan karakter wildcard membantu kalian mencari teks berdasarkan pola tertentu. Fitur ini berguna ketika kalian hanya mengetahui sebagian nama produk, pelanggan, atau informasi lainnya.
XLOOKUP mendukung karakter bintang (*) untuk mewakili sejumlah karakter dan tanda tanya (?) untuk mewakili satu karakter. Kalian perlu mengaktifkan mode wildcard menggunakan match_mode bernilai 2.
Contohnya, kalian ingin mencari divisi karyawan yang namanya diawali dengan “Cit”. Gunakan rumus berikut berdasarkan tabel sebelumnya:
=XLOOKUP("Cit*";B2:B6;C2:C6;"Tidak ditemukan";2)Rumus tersebut menghasilkan Operasional karena Excel menemukan nama Citra yang sesuai dengan pola “Cit*”.
Jika kalian ingin mencari teks yang mengandung kata tertentu pada posisi mana pun, tempatkan karakter bintang di awal dan akhir kata pencarian.
=XLOOKUP("*tri*";B2:B6;C2:C6;"Tidak ditemukan";2)Dalam contoh tersebut, Excel tetap menemukan Citra karena namanya mengandung rangkaian huruf “tri”.
Perlu kalian ingat bahwa wildcard mencari pola teks, bukan otomatis mengembalikan seluruh baris yang cocok. Jika ada beberapa nama yang memenuhi pola, XLOOKUP tetap mengambil kecocokan pertama sesuai arah pencariannya.
Cara Menggunakan XLOOKUP Beda Sheet di Excel
Cara menggunakan XLOOKUP beda sheet di Excel dapat membantu kalian mengambil informasi dari lembar kerja lainnya dalam satu workbook. Teknik ini sangat berguna saat kalian memisahkan data utama dan laporan berdasarkan fungsi masing-masing.
Misalnya, tabel karyawan berada pada sheet bernama Data, sedangkan kalian membuat laporan pada sheet lainnya. Kode karyawan yang ingin dicari berada pada F2 di sheet laporan.
Untuk menampilkan nama karyawan dari sheet Data, gunakan rumus berikut:
=XLOOKUP(F2;Data!A2:A6;Data!B2:B6;"Tidak ditemukan")Excel akan mencari kode yang terdapat pada F2 di kolom A sheet Data, kemudian mengambil nama dari kolom B pada sheet yang sama.
Jika nama sheet mengandung spasi, kalian harus menambahkan tanda petik tunggal pada nama sheet tersebut.
=XLOOKUP(F2;'Data Karyawan'!A2:A6;'Data Karyawan'!B2:B6;"Tidak ditemukan")Pastikan nama sheet sesuai dengan nama lembar kerja yang sebenarnya. Selain itu, kalian dapat menggunakan referensi absolut agar rentang pencarian tidak bergeser ketika rumus disalin ke sel lain.
=XLOOKUP(F2;Data!$A$2:$A$6;Data!$B$2:$B$6;"Tidak ditemukan")Tanda dolar ($) mengunci bagian kolom dan baris pada referensi sel. Dengan begitu, kalian dapat menyalin rumus tanpa mengubah rentang sumber datanya.
Perbedaan XLOOKUP dan VLOOKUP di Excel
Perbedaan XLOOKUP dan VLOOKUP di Excel terlihat dari susunan argumen, arah pencarian, dan kemampuan pengembalian hasil. Keduanya sama-sama membantu menemukan informasi, tetapi memiliki karakteristik yang berbeda.
VLOOKUP menggunakan nomor indeks kolom untuk menentukan informasi yang ingin diambil. Sebaliknya, XLOOKUP memungkinkan kalian memilih rentang hasil secara langsung.
Berikut perbandingan kemampuan kedua fungsi tersebut.
| Aspek | XLOOKUP | VLOOKUP |
|---|---|---|
| Arah pencarian | Dapat mengambil hasil dari kiri maupun kanan | Mengambil hasil ke kanan dari kolom pencarian pertama |
| Kecocokan default | Persis | Perkiraan |
| Indeks kolom | Tidak diperlukan | Diperlukan |
| Pesan jika tidak ditemukan | Tersedia melalui argumen khusus | Memerlukan penanganan tambahan, seperti IFNA |
| Beberapa kolom hasil | Dapat menggunakan satu rumus | Umumnya memerlukan beberapa rumus |
| Dukungan Excel lama | Terbatas pada versi yang mendukung | Tersedia pada lebih banyak versi lama |
Untuk memahami perbedaannya, perhatikan rumus VLOOKUP berikut:
=VLOOKUP(F2;A2:D6;2;FALSE)Rumus tersebut mencari kode pada F2 dan mengambil informasi dari kolom kedua dalam rentang A2:D6. Argumen FALSE memastikan pencocokan dilakukan secara persis.
Sementara itu, rumus XLOOKUP untuk mendapatkan hasil yang sama adalah:
=XLOOKUP(F2;A2:A6;B2:B6)Rumus XLOOKUP lebih langsung karena kalian cukup menentukan lokasi pencarian dan lokasi hasil. Selain itu, pencocokan persis sudah menjadi pengaturan default sehingga tidak memerlukan argumen tambahan.
Meski demikian, VLOOKUP tetap relevan ketika kalian harus mengolah file yang digunakan bersama pengguna Excel versi lama. Pilih fungsi berdasarkan kebutuhan, struktur data, dan kompatibilitas aplikasi.
Penyebab Rumus XLOOKUP Tidak Berfungsi dan Cara Mengatasinya
Penyebab rumus XLOOKUP tidak berfungsi dapat berasal dari kesalahan penulisan, ketidaksesuaian tipe data, atau keterbatasan versi Excel. Oleh sebab itu, kalian perlu memahami beberapa masalah yang sering muncul.
XLOOKUP Menghasilkan Error #N/A
XLOOKUP menghasilkan error #N/A ketika Excel tidak menemukan nilai yang sesuai dengan kriteria pencarian.
Pertama, periksa apakah data benar-benar tersedia pada lookup_array. Selanjutnya, pastikan tidak ada perbedaan format yang menyebabkan nilai gagal dicocokkan.
Sebagai contoh, angka 123 yang tersimpan sebagai angka dapat berbeda dari “123” yang tersimpan sebagai teks. Karena itu, samakan tipe data pada nilai pencarian dan kolom sumber.
Selain itu, perhatikan spasi tambahan yang tidak terlihat. Kalian dapat menggunakan TRIM untuk membersihkan spasi berlebih pada data teks, kemudian menguji kembali rumusnya.
XLOOKUP Menghasilkan Error #NAME?
XLOOKUP menghasilkan error #NAME? apabila Excel tidak mengenali nama fungsi atau terdapat bagian rumus yang tidak sesuai.
Masalah ini dapat terjadi ketika kalian memakai versi Excel yang belum mendukung XLOOKUP. Oleh karena itu, periksa versi aplikasi melalui menu Account atau Tentang Excel sesuai tampilan aplikasi kalian.
Jika versi Excel sudah mendukung fungsi tersebut, periksa kembali ejaan XLOOKUP dan penulisan rumus. Pastikan pula teks yang digunakan sebagai argumen sudah berada di dalam tanda petik ganda.
XLOOKUP Menghasilkan Error #VALUE!
XLOOKUP menghasilkan error #VALUE! dalam kondisi tertentu ketika argumen atau ukuran rentang tidak sesuai.
Misalnya, kalian menentukan lookup_array sepanjang 10 baris, tetapi return_array hanya mencakup 5 baris. Perbedaan tersebut dapat membuat Excel gagal mengembalikan hasil yang valid.
Untuk mengatasinya, periksa kembali referensi sel dan pastikan rentang pencarian serta rentang hasil memiliki ukuran yang sesuai pada arah pencarian.
XLOOKUP Menghasilkan Error #SPILL!
XLOOKUP menghasilkan error #SPILL! ketika rumus hendak menampilkan beberapa hasil sekaligus, tetapi area yang diperlukan tidak tersedia.
Contohnya, kalian menggunakan return_array dengan tiga kolom, sementara salah satu sel hasil sudah berisi data. Excel tidak dapat menempatkan seluruh hasil pada area tersebut.
Solusinya, kosongkan sel yang menghalangi keluaran rumus. Selain itu, periksa keberadaan sel gabungan atau batas tabel Excel yang dapat membatasi proses spill.
Rumus XLOOKUP Tidak Bisa Menggunakan Titik Koma
Rumus XLOOKUP tidak bisa menggunakan titik koma pada beberapa pengaturan Excel karena aplikasi memakai pemisah argumen yang berbeda.
Jika Excel kalian menggunakan tanda koma, ubah rumus berikut:
=XLOOKUP(F2;A2:A6;B2:B6)Menjadi:
=XLOOKUP(F2,A2:A6,B2:B6)Perbedaan pemisah biasanya berkaitan dengan pengaturan regional atau sistem. Karena itu, gunakan karakter pemisah yang dikenali oleh aplikasi Excel kalian.
Tips Menggunakan XLOOKUP agar Lebih Efektif
Tips menggunakan XLOOKUP agar lebih efektif berfokus pada ketepatan referensi, konsistensi data, dan pengelolaan tabel. Dengan menerapkan beberapa kebiasaan berikut, kalian dapat membuat rumus yang lebih mudah digunakan dalam jangka panjang.
Gunakan Referensi Absolut Saat Menyalin Rumus
Gunakan referensi absolut saat menyalin rumus agar rentang sumber data tetap berada pada posisi yang sama.
Contohnya, kalian dapat menggunakan rumus berikut:
=XLOOKUP(F2;$A$2:$A$6;$B$2:$B$6;"Tidak ditemukan")Ketika kalian menyalin rumus ke bawah, referensi F2 dapat berubah mengikuti baris baru. Namun, rentang A2:A6 dan B2:B6 tetap terkunci.
Cara ini membantu mencegah kesalahan akibat pergeseran referensi yang tidak disengaja.
Manfaatkan Excel Table untuk Data yang Bertambah
Manfaatkan Excel Table untuk data yang bertambah secara berkala. Fitur ini memungkinkan kalian mengelola rentang dengan referensi terstruktur yang lebih mudah dibaca.
Pilih tabel sumber, kemudian tekan Ctrl + T untuk mengubahnya menjadi Excel Table. Pastikan pilihan tabel memiliki header sudah sesuai, lalu tentukan nama tabel melalui pengaturan Table Design.
Misalnya, kalian memberi nama TabelKaryawan pada tabel sebelumnya. Rumus pencariannya dapat ditulis sebagai berikut:
=XLOOKUP(F2;TabelKaryawan[ID Karyawan];TabelKaryawan[Nama];"Tidak ditemukan")Referensi tersebut mengikuti kolom berdasarkan nama header. Ketika kalian menambahkan baris baru ke dalam tabel, referensi terstruktur dapat ikut mencakup data tambahan secara otomatis.
Pastikan Kolom Pencarian Memiliki Data yang Konsisten
Pastikan kolom pencarian memiliki data yang konsisten, terutama ketika kalian memakai kode produk, nomor transaksi, atau identitas pelanggan.
Hindari penggunaan format yang berbeda untuk jenis informasi yang sama. Sebagai contoh, jangan mencampurkan kode 00123 dalam bentuk teks dengan angka 123 apabila angka nol di depan merupakan bagian penting dari identitas.
Selain itu, periksa data ganda. Jika beberapa baris memiliki kode yang sama, XLOOKUP secara default mengambil kecocokan pertama. Karena itu, gunakan identitas unik apabila kalian membutuhkan hasil yang benar-benar spesifik.
Pilih Mode Pencocokan Sesuai Kebutuhan
Pilih mode pencocokan sesuai kebutuhan agar hasil rumus tidak menimbulkan kesalahan interpretasi.
Untuk mencari ID, NIK, nomor pesanan, atau kode produk, gunakan pencocokan persis. Sebaliknya, untuk menentukan kategori nilai, tarif, atau batas diskon, kalian dapat memakai pencocokan mendekati.
Hindari penggunaan pencarian biner apabila kalian belum memastikan pengurutan data. Meskipun metode tersebut dapat berguna pada rentang yang sangat besar, hasilnya bergantung pada urutan data yang benar.
Kesimpulan
Cara menggunakan XLOOKUP di Excel sebenarnya cukup mudah apabila kalian memahami tiga komponen utamanya, yaitu nilai pencarian, rentang pencarian, dan rentang hasil. Dengan ketiga argumen tersebut, kalian sudah dapat mencari informasi dari berbagai tabel secara otomatis.
Selain pencarian dasar, XLOOKUP menyediakan berbagai kemampuan tambahan, seperti mengambil data dari kolom sebelah kiri, menampilkan beberapa kolom sekaligus, mencari kecocokan mendekati, serta menemukan kemunculan data terakhir. Kalian juga dapat menggunakan karakter wildcard untuk mencari informasi berdasarkan pola teks.
Dibandingkan VLOOKUP, XLOOKUP menawarkan struktur rumus yang lebih fleksibel dan menjadikan pencocokan persis sebagai pengaturan default. Namun, pastikan versi Microsoft Excel yang kalian gunakan sudah mendukung fungsi tersebut.
Untuk mendapatkan hasil yang akurat, perhatikan kesesuaian tipe data, ukuran rentang, dan penulisan argumen. Selanjutnya, biasakan menggunakan referensi absolut atau Excel Table ketika mengelola data yang terus berkembang.
Dengan memahami berbagai contoh di atas, kalian dapat memanfaatkan XLOOKUP untuk mempercepat pekerjaan administrasi, menyusun laporan, serta mengelola data secara lebih efisien tanpa melakukan pencarian manual berulang kali.