Fungsi XLOOKUP dan Cara Menggunakannya dalam Mengolah Data di Microsoft Excel
Menyajikan beragam informasi terbaru, terkini dan mengedukasi.
·waktu baca 7 menit
Tulisan dari Kabar Harian tidak mewakili pandangan dari redaksi kumparan
PerbesarMicrosoft Excel sebagai program perangkat lunak untuk mengolah data memiliki berbagai fitur bermanfaat. Salah satunya adalah XLOOKUP. Fungsi XLOOKUP beserta cara menggunakannya perlu dipahami agar lebih mudah dalam mengolah data melalui Excel.
Apabila dalam mengolah data di Excel pernah menggunakan VLOOKUP dan HLOOKUP, maka tentu tidak asing dengan XLOOKUP. Secara fungsi, ketiga rumus ini tidak berbeda jauh. Namun, tetap terdapat perbedaan antara XLOOKUP dengan VLOOKUP dan HLOOKUP.
Walau terdapat perbedaan di antara ketiga rumus tersebut, namun ketiganya dapat bermanfaat untuk mempermudah proses pengolahan data yang dilakukan melalui Excel. Dengan menguasai rumus XLOOKUP, maka mengolah data akan jadi lebih cepat dan praktis.
Daftar isi
Daftar isi
Daftar isi
Apa Itu XLOOKUP?
Sebelum membahas mengenai fungsi dari XLOOKUP, sebenarnya apa yang dimaksud dengan XLOOKUP? Dikutip dari buku Mahir Fungsi Excel Terapan dalam Olah Data, Imam Supriadi, ST., MM, (2022:118), XLOOKUP merupakan fungsi dalam Excel yang digunakan untuk mencari sebuah range atau array.
Selain itu XLOOKUP juga dapat mengembalikan item yang berhubungan dengan kecocokan pertama. Jika tidak ada kecocokan, maka XLOOKUP dapat mengembalikan kecocokan yang memiliki kemiripan tersebut.
XLOOKUP juga dapat digunakan untuk pencarian data secara vertikal atau horizontal, ke kiri ata ke atas, maupun pencarian dengan beberapa kriteria dan mengembalikan seluruh kolom atau baris data, bukan hanya satu nilai.
Melakukan pencarian dengan XLOOKUP dapat membantu menemukan kecocokan persis, kecocokan perkiraan, maupun kecocokan sebagian. Sehingga XLOOKUP dapat membantu mencari data dengan lebih mudah.
Bila dilihat dari definisi dan kegunaan tersebut, sekilas XLOOKUP ini mirip dengan VLOOKUP. Namun, yang membedakannya XLOOKUP dapat digunakan untuk melihat data secara vertikal maupun horizontal, sementara VLOOKUP hanya mampu membaca data secara vertikal.
Hal inilah yang menjadikan XLOOKUP lebih bagus dalam banyak hal. Tidak heran jika banyak orang memilih menggunakan XLOOKUP saat melakukan pengolahan data, agar lebih mudah dalam mencari data sesuai yang diperlukan.
Fungsi XLOOKUP dan Rumusnya
Dalam menggunakan XLOOKUP saat mengolah data, terdapat 6 argumen yang menjadi fungsi dari XLOOKUP. Berikut adalah fungsi XLOOKUP dan cara menggunakannya dalam mengolah data di Microsoft Excel dikutip dari buku Mahir Fungsi Excel Terapan dalam Olah Data, Imam Supriadi, ST., MM, (2022:118).
1. Tiga Argumen yang Diperlukan:
lookup_value: Nilai yang dicari.
lookup_array: Range atau array untuk pencarian.
return_array: Range atau array yang akan dikembalikan.
2. Tiga Argumen Opsional:
if_not_found: Nilai atau pesan yang ditampilkan jika tidak ada nilai yang cocok.
match_mode: Ketentuan jenis kecocokan apabila defaultnya adalah sama persis. Apabila bagian ini tidak diisi atau diisi dengan nilai 0, maka akan dilakukan pencocokan sama persis.
search_mode: Penentuan jenis pencarian, seperti dimulai dari atas ke bawah dan sebagainya. Apabila tidak diisi atau diisi dengan nilai 1, maka dilakukan pencarian dari pertama hingga terakhir.
Seperti yang sudah dituliskan di atas, diketahui bahwa terdapat tiga argumen awal yang wajib digunakan. Sementara ketiga argumen lainnya hanya sebagai opsional yang berarti tidak harus digunakan.
Sementara untuk bisa menggunakan XLOOKUP ini dibutuhkan rumus. Adapun rumus XLOOKUP adalah sebagai berikut:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Keterangan sintaks:
lookup_value: Nilai yang akan ditelusuri.
lookup_array: Array atau rentang yang dipertimbangkan untuk penelusuran. Rentang ini harus berupa baris atau kolom tunggal.
return_array: Array atau rentang dipertimbangkan untuk hasil. Ukuran kolom atau baris rentang ini harus sama dengan ukuran kolom lookup_array, tergantung dengan cara pencarian dilakukan.
if_not_found: Nilai yang akan ditampilkan jika tidak ditemukan adanya kecocokan.
match_mode: Cara menemukan kecocokan untuk lookup_value dengan nilai:
- 0 adalah untuk pencocokan persis.
- 1 adalah untuk pencocokan persis atau nilai berikutnya yang lebih besar dari lookup_value.
- -1 adalah untuk pencocokan persis atau nilai berikutnya yang lebih kecil dari lookup_value.
- 2 digunakan untuk pencocokan karakter pengganti.
search_mode: Cara yang digunakan untuk menelusuri lookup_array dengan nilai:
- 1 adalah untuk menelusuri dari entri pertama hingga terakhir
- -1 adalah untuk menelusuri dengan urutan terbalik, dari entri terakhir ke entri pertama.
- 2 adalah untuk menelusuri rentang dengan penelusuran biner yang rentangnya diurutkan dalam urutan naik terlebih dahulu.
- -2 adalah untuk menelusuri rentang dengan penulisan biner yang rentangnya diurutkan dalam urutan menurun terlebih dahulu.
Apabila terdapat kendala saat penggunaan XLOOKUP, seperti muncul nilai error, maka dapat disebabkan oleh berbagai kemungkinan. Adapun beberapa nilai tersebut antara lain sebagai berikut:
Nilai error #NAME?: Terjadi saat mencoba XLOOKUP di versi Excel yang lebih lama atau juga karena tidak sengaja salah mengeja nama fungsi.
Nilai error #VALUE!: Terjadi jika range atau rentang pencarian dan range atau rentang pengembalian memiliki dimensi yang tidak kompatibel.
Nilai error #SPILL!: Terjadi karena beberapa penyebab, seperti jika satu atau lebih sel dalam range tumpahan tidak sepenuhnya kosong.
Nilai error #NA: Terjadi jika nilai pencarian tidak ditemukan.
Nilai error #REF!: Terjadi apabila fungsi XLOOKUP digunakan di antara workbook yang berbeda dan workbook sumber ditutup.
Cara Menggunakan XLOOKUP di Microsoft Excel
Setelah mengetahui fungsi dari XLOOKUP dan rumusnya, maka ada baiknya untuk tahu cara menggunakan XLOOKUP saat mengolah data di Microsoft Excel. Untuk bisa menggunakan XLOOKUP, maka harus disiapkan data yang akan diolah terlebih dahulu. Berikut contohnya:
1. Cara 1
Pada contoh ini terdapat penggunaan XLOOKUP untuk mencari nama negara dalam rentang, lalu mengembalikan kode negara teleponnya. Hal ini mencakup argumen lookup_value (sel F2), lookup_array (rentang B2:B11), dan return_array (rentang D2:D11). Ini tidak menyertakan argumen match_mode , karena XLOOKUP menghasilkan kecocokan persis secara default.
Catatan: XLOOKUP menggunakan array pencarian dan array yang dikembalikan, sedangkan VLOOKUP menggunakan array tabel tunggal diikuti dengan angka indeks kolom. Rumus VLOOKUP yang setara dalam hal ini adalah: =VLOOKUP(F2,B2:D11,3,FALSE)
2. Cara 2
Pada contoh ini digunakan untuk mencari informasi karyawan berdasarkan nomor ID karyawan. Tidak seperti VLOOKUP, XLOOKUP dapat mengembalikan array dengan beberapa item, sehingga rumus tunggal dapat mengembalikan nama karyawan dan departemen dari sel C5:D14.
3. Cara 3
Berikutnya adalah contoh apabila menambah argumen if_not_found pada tabel yang ada pada contoh sebelumnya.
4. Cara 4
Contoh selanjutnya adalah terlihat di kolom C untuk pendapatan pribadi yang dimasukkan dalam sel E2, dan menemukan tarif pajak yang cocok di kolom B. Ini mengatur argumen if_not_found untuk mengembalikan 0 (nol) jika tidak ada yang ditemukan.
Kemudian argumen match_mode diatur ke 1, yang berarti fungsi akan mencari kecocokan yang persis, dan jika tidak dapat menemukannya, fungsi mengembalikan item yang lebih besar berikutnya.
Akhirnya, argumen search_mode diatur ke 1, yang berarti fungsi akan mencari dari item pertama ke yang terakhir.
Catatan: Kolom lookup_array XARRAY berada di sebelah kanan kolom return_array , sedangkan VLOOKUP hanya dapat terlihat dari kiri ke kanan.
5. Cara 5
Contoh lainnya adalah menggunakan fungsi XLOOKUP bertumpuk untuk melakukan kecocokan vertikal dan horizontal. Pertama mencari Laba Kotor di kolom B, lalu mencari Qtr1 di baris atas tabel (rentang C5:F5), dan akhirnya mengembalikan nilai di irisan keduanya. Ini mirip dengan menggunakan fungsi INDEX dan MATCH bersama-sama.
Catatan: Rumus dalam sel D3:F3 adalah: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))).
6. Cara 6
Terakhir adalah contoh menggunakan fungsi SUM, dan dua fungsi XLOOKUP bertumpuk, untuk menjumlahkan semua nilai di antara dua rentang. Dalam hal ini, untuk menjumlahkan nilai untuk anggur, pisang, dan termasuk pir, yang berada di antara keduanya.
Rumus dalam sel E3 adalah: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Dari beberapa contoh tersebut, maka dapat diikuti sebagai panduan dalam menggunakan XLOOKUP pada pengolahan data. Dengan menggunakan XLOOKUP, maka pencarian data akan jadi lebih mudah dan praktis.
Namun, perlu diketahui bahwa pada artikel ini dibuat, XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019 sesuai informasi dari situs resmi Microsoft di support.microsoft.com. Sehingga untuk bisa menggunakannya bisa dilakukan melalui Microsoft 365 atau Excel versi terbaru, seperti Excel 2021.
Baca juga: Cara VLOOKUP Nama yang Sama di Excel untuk Mengolah Data
Itu dia informasi mengenai fungsi XLOOKUP beserta rumus dan cara menggunakannya yang bisa dipelajari. Apabila telah memahami XLOOKUP ini, maka mencari data di lembar kerja Excel akan jadi lebih mudah dan praktis. (PRI)