Rumus XLOOKUP Excel – Mencari data tertentu dari tabel merupakan salah satu pekerjaan yang paling sering dilakukan ketika menggunakan Microsoft Excel. Ketika jumlah data masih sedikit, pencarian manual mungkin tidak menjadi masalah. Namun, jika spreadsheet berisi ratusan atau bahkan ribuan baris, cara tersebut tentu tidak efisien.

Microsoft Excel menyediakan berbagai fungsi untuk membantu pencarian data, seperti VLOOKUP, HLOOKUP, INDEX MATCH, dan XLOOKUP. Di antara fungsi tersebut, XLOOKUP menjadi salah satu pilihan yang sangat praktis karena memiliki sintaks yang lebih fleksibel dan mendukung berbagai kebutuhan pencarian.

Dengan rumus XLOOKUP Excel, Anda dapat mencari data berdasarkan suatu nilai kemudian mengambil informasi yang sesuai dari kolom atau baris lain. Fungsi ini juga dapat mencari data ke kiri maupun ke kanan sehingga tidak memiliki keterbatasan arah pencarian seperti VLOOKUP.

Artikel ini membahas cara menggunakan XLOOKUP dari dasar, contoh rumus, pencarian dengan beberapa kondisi, penggunaan nilai pengganti ketika data tidak ditemukan, hingga perbandingan XLOOKUP dengan VLOOKUP dan INDEX MATCH.

Apa Itu XLOOKUP Excel?

XLOOKUP adalah fungsi Excel yang digunakan untuk mencari sebuah nilai pada suatu rentang kemudian mengembalikan nilai yang berhubungan dari rentang lainnya.

Secara sederhana, cara kerjanya adalah:

Tentukan data yang ingin dicari.
Tentukan lokasi pencarian.
Tentukan lokasi data hasil.
Excel mencari nilai tersebut.
Excel mengembalikan hasil yang sesuai.

Contohnya, Anda memiliki daftar kode produk dan harga. Jika ingin mengetahui harga berdasarkan kode produk, XLOOKUP dapat melakukan pencarian tersebut secara otomatis.

Sintaks Rumus XLOOKUP

Bentuk dasar XLOOKUP adalah:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Keterangan:

lookup_value adalah nilai yang ingin dicari.
lookup_array adalah rentang tempat Excel mencari nilai.
return_array adalah rentang yang berisi hasil yang ingin ditampilkan.
if_not_found adalah nilai yang ditampilkan jika data tidak ditemukan.
match_mode menentukan cara pencocokan.
search_mode menentukan arah atau metode pencarian.

Untuk penggunaan sehari-hari, tiga argumen pertama biasanya sudah cukup.

Contoh Data XLOOKUP Excel

Misalnya terdapat tabel produk berikut:

Kode	Produk	Kategori	Harga
P001	Laptop ASUS	Elektronik	8.000.000
P002	Mouse Logitech	Aksesori	250.000
P003	Keyboard Mechanical	Aksesori	750.000
P004	Monitor 24 Inch	Elektronik	2.000.000
P005	Printer Epson	Elektronik	1.800.000

Misalnya kode produk yang ingin dicari berada di sel F2.

Jika F2 berisi P003, gunakan:

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

Hasilnya:

750.000

XLOOKUP mencari P003 pada A2 kemudian mengambil harga yang sesuai dari D2.

Cara Membaca Rumus XLOOKUP

Rumus:

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

dapat dibaca sebagai:

Cari nilai yang terdapat pada F2 di dalam rentang A2, kemudian tampilkan nilai yang sesuai dari D2.

Pemahaman ini menjadi dasar untuk menggunakan XLOOKUP dalam berbagai situasi.

XLOOKUP dengan Nilai Jika Data Tidak Ditemukan

Salah satu fitur yang sangat berguna adalah kemampuan menentukan pesan ketika data tidak ditemukan.

Contoh:

=XLOOKUP(F2,A2:A6,D2:D6,"Produk tidak ditemukan")

Jika kode yang dicari tidak ada, Excel akan menampilkan:

Produk tidak ditemukan

Cara ini lebih informatif dibandingkan membiarkan Excel menampilkan kesalahan #N/A.

XLOOKUP Menggunakan Referensi Sel

Sebaiknya nilai pencarian diletakkan pada sel tertentu agar rumus dapat digunakan berulang kali.

Misalnya:

F2 = kode produk.
G2 = hasil pencarian harga.

Gunakan:

=XLOOKUP(F2,A2:A100,D2:D100,"Data tidak ditemukan")

Ketika isi F2 diganti, hasil pada G2 akan otomatis berubah.

Teknik ini sangat berguna untuk membuat formulir pencarian sederhana di Excel.

XLOOKUP Mencari Data ke Kiri

Salah satu keunggulan XLOOKUP adalah dapat mengembalikan data yang berada di sebelah kiri kolom pencarian.

Misalnya:

Nama Produk	Kode
Laptop ASUS	P001
Mouse Logitech	P002
Keyboard Mechanical	P003

Jika kode produk berada di B2 dan nama produk berada di A2, Anda dapat menggunakan:

=XLOOKUP(E2,B2:B4,A2:A4)

Jika E2 berisi P003, hasilnya:

Keyboard Mechanical

Hal ini membuat XLOOKUP lebih fleksibel daripada VLOOKUP yang secara normal hanya mengambil hasil ke arah kanan.

XLOOKUP Mencari Data dari Kanan ke Kiri

Tidak ada persyaratan bahwa kolom pencarian harus berada di sebelah kiri hasil.

Contoh:

=XLOOKUP(E2,D2:D100,A2:A100)

Excel mencari nilai pada kolom D lalu mengembalikan data dari kolom A.

Fitur ini sangat berguna ketika struktur tabel sudah ditentukan dan tidak dapat diubah.

XLOOKUP dengan Pencocokan Tepat

Secara umum, XLOOKUP menggunakan pencocokan yang tepat secara default.

Contohnya:

=XLOOKUP("P003",A2:A100,D2:D100)

Excel akan mencari P003 dan mengembalikan hasil yang cocok.

Ini sangat cocok untuk:

Kode produk.
Nomor pelanggan.
NIS siswa.
ID karyawan.
Nomor transaksi.

XLOOKUP dengan Wildcard

XLOOKUP juga dapat digunakan untuk pencarian dengan wildcard jika match mode yang sesuai dipilih.

Karakter wildcard yang umum digunakan adalah:

* untuk sejumlah karakter.
? untuk satu karakter.

Contohnya:

=XLOOKUP("Laptop*",A2:A100,D2:D100,"Tidak ditemukan",2)

Rumus tersebut dapat digunakan untuk menemukan teks yang memiliki pola tertentu.

XLOOKUP untuk Mencari Nilai Terdekat

Selain pencocokan tepat, XLOOKUP memiliki mode pencarian yang memungkinkan Anda mencari nilai terdekat.

Contohnya dapat digunakan untuk menentukan kategori berdasarkan batas nilai.

Misalnya tabel:

Minimal Nilai	Grade
0	D
60	C
70	B
85	A

Jika nilai siswa berada di E2, Anda dapat menggunakan pencarian dengan mode yang sesuai untuk mendapatkan grade berdasarkan batas nilai.

Pendekatan seperti ini berguna dalam:

Penilaian siswa.
Kategori harga.
Tingkatan bonus.
Klasifikasi performa.
Sistem komisi.

XLOOKUP dengan Dua Kondisi

Dalam beberapa kasus, satu kriteria tidak cukup.

Misalnya data memiliki:

Produk.
Wilayah.
Penjualan.

Anda ingin mencari nilai berdasarkan produk dan wilayah sekaligus.

Salah satu pendekatan adalah menggabungkan dua kondisi menjadi satu lookup value.

Contoh:

=XLOOKUP(G2&H2,A2:A100&B2:B100,C2:C100,"Data tidak ditemukan")

Jika:

G2 berisi produk.
H2 berisi wilayah.

XLOOKUP akan mencari kombinasi kedua nilai tersebut.

Pada versi Excel yang mendukung dynamic array, pendekatan ini dapat digunakan tanpa membuat kolom bantuan.

XLOOKUP dengan IFERROR

Sebenarnya XLOOKUP sudah menyediakan argumen if_not_found, sehingga IFERROR tidak selalu diperlukan.

Namun, IFERROR tetap berguna ketika XLOOKUP merupakan bagian dari rumus yang lebih kompleks.

Contoh:

=IFERROR(XLOOKUP(F2,A2:A100,D2:D100),"Data tidak tersedia")

Jika terjadi kesalahan apa pun dalam proses tersebut, Excel akan menampilkan pesan yang ditentukan.

XLOOKUP untuk Data Karyawan

XLOOKUP sangat cocok digunakan dalam administrasi karyawan.

Misalnya terdapat data:

ID	Nama	Departemen	Gaji
K001	Andi	Keuangan	7.000.000
K002	Budi	Marketing	6.500.000
K003	Citra	HRD	7.500.000

Jika ID karyawan berada pada F2 dan Anda ingin mengambil nama:

=XLOOKUP(F2,A2:A4,B2:B4,"Karyawan tidak ditemukan")

Untuk mengambil departemen:

=XLOOKUP(F2,A2:A4,C2:C4,"Karyawan tidak ditemukan")

Untuk mengambil gaji:

=XLOOKUP(F2,A2:A4,D2:D4,"Karyawan tidak ditemukan")

Satu data ID dapat digunakan untuk mengambil berbagai informasi.

XLOOKUP untuk Data Siswa

Dalam administrasi sekolah, XLOOKUP dapat membantu mencari informasi siswa.

Misalnya tabel berisi:

NIS.
Nama.
Kelas.
Nilai.

Guru dapat memasukkan NIS pada satu sel kemudian menggunakan XLOOKUP untuk menampilkan data siswa secara otomatis.

Contoh:

=XLOOKUP(F2,A2:A100,B2:B100,"Siswa tidak ditemukan")

Dengan cara tersebut, pencarian data siswa menjadi lebih cepat.

XLOOKUP untuk Laporan Penjualan

Dalam laporan bisnis, XLOOKUP dapat digunakan untuk mengambil:

Nama produk.
Harga.
Kategori.
Nama sales.
Wilayah.
Target penjualan.

Misalnya kode produk berada di F2 dan harga berada di kolom D:

=XLOOKUP(F2,A2:A100,D2:D100,0)

Hasil pencarian dapat kemudian digunakan dalam rumus lain untuk menghitung total transaksi.

XLOOKUP untuk Mengambil Banyak Kolom

Pada Excel yang mendukung dynamic array, XLOOKUP dapat mengembalikan beberapa kolom sekaligus.

Misalnya Anda ingin mengambil Nama Produk, Kategori, dan Harga:

=XLOOKUP(F2,A2:A100,B2:D100,"Tidak ditemukan")

Jika data ditemukan, hasil dapat mengisi beberapa sel secara otomatis.

Fitur ini membuat XLOOKUP semakin praktis untuk membuat sistem pencarian data.

Perbedaan XLOOKUP dan VLOOKUP

XLOOKUP menawarkan sejumlah kelebihan dibandingkan VLOOKUP.

Aspek	XLOOKUP	VLOOKUP
Pencarian ke kanan	Ya	Ya
Pencarian ke kiri	Ya	Tidak secara langsung
Nomor indeks kolom	Tidak perlu	Perlu
Nilai jika tidak ditemukan	Bisa ditentukan	Biasanya perlu IFERROR
Pencocokan tepat	Didukung	Didukung
Pencarian dari bawah	Didukung	Tidak secara langsung
Dynamic Array	Didukung	Lebih terbatas

VLOOKUP tetap berguna, terutama jika bekerja dengan file Excel lama. Namun, jika versi Excel yang digunakan mendukung XLOOKUP, fungsi tersebut memberikan lebih banyak fleksibilitas.

Perbedaan XLOOKUP dan INDEX MATCH

INDEX MATCH merupakan kombinasi rumus yang sudah lama digunakan untuk pencarian data fleksibel.

XLOOKUP memiliki beberapa kelebihan karena sintaksnya lebih sederhana.

Contoh INDEX MATCH:

=INDEX(D2:D100,MATCH(F2,A2:A100,0))

Dengan XLOOKUP:

=XLOOKUP(F2,A2:A100,D2:D100)

Keduanya dapat menghasilkan pencarian yang sama, tetapi XLOOKUP lebih mudah dibaca bagi banyak pengguna.

Meski demikian, INDEX MATCH tetap penting dipelajari karena masih banyak file kerja yang menggunakannya.

Kesalahan yang Sering Terjadi Saat Menggunakan XLOOKUP

1. Rentang Tidak Sejajar

Pastikan lookup_array dan return_array memiliki jumlah baris atau kolom yang sesuai.

Contoh yang benar:

=XLOOKUP(F2,A2:A100,D2:D100)

Hindari rentang yang memiliki ukuran berbeda secara tidak sengaja.

2. Data Mengandung Spasi

Nilai P001 dan P001 dapat dianggap berbeda.

Jika data berasal dari sumber eksternal, lakukan pembersihan data sebelum melakukan pencarian.

3. Salah Memilih Lookup Array

Pastikan kolom yang digunakan untuk pencarian benar-benar berisi nilai yang dicari.

4. Versi Excel Tidak Mendukung XLOOKUP

XLOOKUP tidak tersedia pada semua versi Excel lama. Jika fungsi tersebut menghasilkan #NAME?, periksa versi Excel yang digunakan.

Jika XLOOKUP tidak tersedia, alternatifnya adalah menggunakan VLOOKUP, HLOOKUP, atau kombinasi INDEX MATCH.

Tips Menggunakan XLOOKUP

Agar rumus lebih mudah digunakan dan dipelihara, perhatikan beberapa tips berikut:

Gunakan argumen if_not_found untuk memberikan pesan yang jelas.
Gunakan referensi sel agar rumus lebih dinamis.
Pastikan data pencarian bersih.
Gunakan Excel Table untuk data yang sering bertambah.
Gunakan referensi terstruktur agar rumus lebih mudah dibaca.
Gunakan XLOOKUP untuk menggantikan pencarian manual.
Untuk pencarian dengan banyak kondisi, pertimbangkan penggunaan helper column atau kombinasi beberapa fungsi.

Contoh XLOOKUP dengan Excel Table

Jika data telah diubah menjadi Table bernama DataProduk, rumus dapat ditulis lebih mudah dibaca.

Misalnya:

=XLOOKUP(F2,DataProduk[Kode],DataProduk[Harga],"Tidak ditemukan")

Keuntungan menggunakan Table adalah referensi dapat mengikuti pertambahan data secara otomatis.

Cara ini sangat cocok untuk laporan yang rutin diperbarui.

XLOOKUP untuk Membuat Sistem Pencarian Sederhana

XLOOKUP dapat digunakan untuk membuat halaman pencarian sederhana.

Misalnya Anda memiliki satu sel input:

Kode Produk: P003

Kemudian di bawahnya terdapat:

Nama Produk: Keyboard Mechanical

Kategori: Aksesori

Harga: Rp750.000

Masing-masing informasi dapat mengambil data dari tabel menggunakan XLOOKUP.

Contoh:

=XLOOKUP(B2,DataProduk[Kode],DataProduk[Nama])

=XLOOKUP(B2,DataProduk[Kode],DataProduk[Kategori])

=XLOOKUP(B2,DataProduk[Kode],DataProduk[Harga])

Dengan desain sederhana, Excel dapat berfungsi seperti database pencarian mini.

FAQ Rumus XLOOKUP Excel

Apa itu rumus XLOOKUP Excel?

XLOOKUP adalah fungsi Excel untuk mencari sebuah nilai dalam suatu rentang dan mengembalikan data yang sesuai dari rentang lainnya.

Apa kelebihan XLOOKUP dibandingkan VLOOKUP?

XLOOKUP dapat mencari data ke kiri maupun kanan, tidak membutuhkan nomor indeks kolom, dapat menentukan hasil ketika data tidak ditemukan, dan memiliki pilihan pencarian yang lebih fleksibel.

Apakah XLOOKUP bisa mencari data ke kiri?

Bisa. Lookup array dan return array XLOOKUP dapat berada di posisi mana pun selama referensinya ditentukan dengan benar.

Apakah XLOOKUP bisa digunakan untuk mencari data berdasarkan dua kriteria?

Bisa. Salah satu caranya adalah menggabungkan beberapa kondisi menjadi satu nilai pencarian atau menggunakan kombinasi fungsi lainnya.

Mengapa XLOOKUP menghasilkan #N/A?

Biasanya karena nilai yang dicari tidak ditemukan dalam lookup array. Anda dapat memberikan argumen if_not_found untuk menampilkan pesan khusus.

Contoh:

=XLOOKUP(F2,A2:A100,D2:D100,"Data tidak ditemukan")

Apakah XLOOKUP tersedia di semua versi Excel?

Tidak. Ketersediaannya bergantung pada versi Excel yang digunakan. Pada versi yang tidak mendukung XLOOKUP, Anda dapat menggunakan VLOOKUP atau INDEX MATCH sebagai alternatif.

Apakah XLOOKUP lebih mudah daripada INDEX MATCH?

Untuk banyak kebutuhan pencarian, XLOOKUP memiliki sintaks yang lebih sederhana karena tidak perlu menggabungkan dua fungsi secara terpisah. Namun, INDEX MATCH tetap berguna untuk kompatibilitas dengan file Excel yang lebih lama.

Kesimpulan

Rumus XLOOKUP Excel merupakan salah satu fungsi pencarian yang sangat berguna untuk mengolah data secara cepat dan fleksibel. Dibandingkan metode pencarian manual, XLOOKUP mampu menemukan informasi secara otomatis berdasarkan nilai tertentu.

Kelebihan utamanya adalah kemampuan mencari data ke kiri maupun kanan, menentukan pesan ketika data tidak ditemukan, serta mendukung berbagai metode pencarian. Fungsi ini dapat digunakan untuk data produk, karyawan, siswa, pelanggan, transaksi, maupun laporan bisnis.

Bagi pelajar, mahasiswa, maupun pekerja yang sering menggunakan Microsoft Excel, memahami XLOOKUP merupakan investasi keterampilan yang sangat bermanfaat. Setelah menguasai fungsi dasar, Anda dapat menggabungkannya dengan IF, SUMIF, SUMIFS, COUNTIF, INDEX MATCH, Pivot Table, dan fitur Excel lainnya untuk membuat sistem pengolahan data yang lebih otomatis dan profesional.

Apa Itu XLOOKUP Excel?

Sintaks Rumus XLOOKUP

Contoh Data XLOOKUP Excel

Cara Membaca Rumus XLOOKUP

XLOOKUP dengan Nilai Jika Data Tidak Ditemukan

XLOOKUP Menggunakan Referensi Sel

XLOOKUP Mencari Data ke Kiri

XLOOKUP Mencari Data dari Kanan ke Kiri

XLOOKUP dengan Pencocokan Tepat

XLOOKUP dengan Wildcard

XLOOKUP untuk Mencari Nilai Terdekat

XLOOKUP dengan Dua Kondisi

XLOOKUP dengan IFERROR

XLOOKUP untuk Data Karyawan

XLOOKUP untuk Data Siswa

XLOOKUP untuk Laporan Penjualan

XLOOKUP untuk Mengambil Banyak Kolom

Perbedaan XLOOKUP dan VLOOKUP

Perbedaan XLOOKUP dan INDEX MATCH

Kesalahan yang Sering Terjadi Saat Menggunakan XLOOKUP

1. Rentang Tidak Sejajar

2. Data Mengandung Spasi

3. Salah Memilih Lookup Array

4. Versi Excel Tidak Mendukung XLOOKUP

Tips Menggunakan XLOOKUP

Contoh XLOOKUP dengan Excel Table

XLOOKUP untuk Membuat Sistem Pencarian Sederhana

FAQ Rumus XLOOKUP Excel

Apa itu rumus XLOOKUP Excel?

Apa kelebihan XLOOKUP dibandingkan VLOOKUP?

Apakah XLOOKUP bisa mencari data ke kiri?

Apakah XLOOKUP bisa digunakan untuk mencari data berdasarkan dua kriteria?

Mengapa XLOOKUP menghasilkan #N/A?

Apakah XLOOKUP tersedia di semua versi Excel?

Apakah XLOOKUP lebih mudah daripada INDEX MATCH?

Kesimpulan

Sebarkan ini:

Posting terkait: