Fungsi XLOOKUP dan Cara Menggunakannya dalam Mengolah Data di Microsoft Excel

Fungsi XLOOKUP, foto hanya ilustrasi: Unsplash/Microsoft 365

Microsoft 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.

Apa Itu XLOOKUP?

Fungsi XLOOKUP, foto hanya ilustrasi: Unsplash/Microsoft 365

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

Fungsi XLOOKUP, foto hanya ilustrasi: Unsplash/Crew

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:

2. Tiga Argumen Opsional:

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:

Keterangan sintaks:

- 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.

- 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:

Cara Menggunakan XLOOKUP di Microsoft Excel

Fungsi XLOOKUP, foto hanya ilustrasi: Unsplash/Guille B

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.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

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.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

3. Cara 3

Berikutnya adalah contoh apabila menambah argumen if_not_found pada tabel yang ada pada contoh sebelumnya.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

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.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

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.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

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.

Contoh tabel fungsi XLOOKUP, Foto: Tangkapan Layar/Microsoft

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)