Selama bertahun-tahun, pengguna Excel mengandalkan fungsi pencarian seperti LOOKUP, VLOOKUP, dan HLOOKUP dengan segala keterbatasannya: arah pencarian yang terbatas, tidak ada penanganan kesalahan, dan sintaks yang kurang fleksibel.
Fungsi XLOOKUP hadir di Excel 365, Excel 2019, dan Excel Web, tidak hanya sebagai penambahan fitur, tapi juga mengubah cara pencarian data di Excel.
Artikel ini membahas kelemahan-kelemahan fungsi lama dan menunjukkan bagaimana XLOOKUP menyelesaikannya.
File: kelebihan-xlookup-excel.xlsx.
Fungsi XLOOKUP Excel: Revolusi Cara Mencari Data
XLOOKUP menyatukan semua kemampuan fungsi lookup lama di dalam satu fungsi. Fungsi ini melakukan pencarian baik vertikal maupun horizontal, mendukung exact dan approximate match, serta dilengkapi penanganan error bawaan—semua dalam satu rumus tunggal.
Sintaks XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
Kelemahan VLOOKUP, HLOOKUP, dan LOOKUP
Sebelum XLOOKUP, pengguna Excel menggunakan fungsi-fungsi berikut untuk mengambil data.
| Fungsi | Keterangan | Kelemahan |
|---|---|---|
| VLOOKUP | Pencarian vertikal |
|
| HLOOKUP | Pencarian horizontal |
|
| LOOKUP | Pencarian bentuk lama |
|
Solusi Mengatasi Keterbatasan Fungsi Lookup Lama di Excel
Sebelum XLOOKUP, pengguna harus menggunakan kombinasi dengan fungsi lain untuk mengatasi keterbatasan-keterbatasan LOOKUP, VLOOKUP, dan HLOOKUP. Kombinasi ini menciptakan kompleksitas yang tidak perlu dan memperbesar peluang kesalahan.
| Solusi | Keterangan | Contoh |
|---|---|---|
| INDEX+MATCH | Solusi untuk pencarian ke kiri/atas. MATCH mencari posisi, INDEX mengembalikan nilai. | |
| IFNA atau IFERROR | Menangani error | |
| CHOOSE atau IF | Memilih jenis lookup | |
| Rumus array | Mengambil beberapa kolom atau baris sekaligus | |
Kelebihan Fungsi XLOOKUP dari Fungsi Lookup Lain
Pencarian Vertikal dan Horizontal dalam Satu Rumus
Kasus: Fungsi VLOOKUP dan HLOOKUP hanya mendukung pencarian satu arah—vertikal untuk VLOOKUP dan horizontal untuk HLOOKUP. Ketika struktur tabel berubah orientasi, pengguna harus mengganti fungsi yang digunakan atau beralih ke kombinasi rumus seperti INDEX dan MATCH.
Solusi XLOOKUP: Kita hanya perlu mengganti parameter lookup_array dan return array sesuai orientasi data, tanpa rumus tambahan.
Tahan terhadap Perubahan Struktur Tabel
Kasus: Fungsi VLOOKUP dan HLOOKUP mengandalkan angka tetap (hardcoded) untuk menentukan kolom atau baris hasil. Jika struktur tabel berubah—misalnya kolom dipindahkan atau baris ditambahkan—dan angka tersebut tidak diperbarui, rumus akan menghasilkan data yang salah atau tidak relevan.
Solusi XLOOKUP: Menggunakan referensi range (lookup_array dan return_array) dan bukan angka tetap. Karena berbasis range, rumus secara otomatis menyesuaikan posisi data saat struktur tabel berubah—tanpa perlu mengedit indeks kolom atau baris secara manual.
Exact Match Default (Tidak Perlu Data Terurut)
Kasus: VLOOKUP dan HLOOKUP secara default menggunakan approximate search, yang membutuhkan data terurut secara ascending. Jika tidak, rumus dapat mengembalikan hasil tidak sesuai atau #N/A.
Solusi XLOOKUP: XLOOKUP menggunakan exact match secara default.
Rumus berikut mencari nama produk berdasarkan kodenya dan mengembalikan hasil sesuai.
=XLOOKUP(E2; A2:A5; B2:B5)
Lookup ke Kiri, Kanan, Atas, Bawah
Kasus: Fungsi VLOOKUP hanya dapat mengambil nilai dari kolom di sebelah kanan kolom pencarian, sedangkan HLOOKUP terbatas pada baris di bawah baris pencarian. Artinya, jika data yang ingin diambil berada di sebelah kiri atau di atas, kedua fungsi ini tidak bisa digunakan secara langsung.
Solusi XLOOKUP: Fungsi XLOOKUP memisahkan parameter lookup_array dan return_array. Selama kedua ukuran array sama, arah pencarian tidak dibatasi.
Rumus berikut mencari Kode produk yang berada di sebelah kiri nama Produk.
=XLOOKUP(D2; B2:B6; A2:A6)
Mengembalikan Beberapa Kolom atau Baris Sekaligus
Kasus: Fungsi VLOOKUP dan HLOOKUP tidak dapat mengembalikan hasil beberapa kolom atau baris sekaligus tanpa bantuan rumus array. Penggunaan array seperti {2;3;4} memang memungkinkan, tetapi rentan kesalahan jika struktur tabel berubah. Untuk membuat rumus lebih fleksibel, pengguna biasanya harus menggabungkan dengan fungsi MATCH dan INDEX, yang membuat rumus menjadi rumit dan sulit dipelihara.
Solusi XLOOKUP: Fungsi XLOOKUP mendukung pengembalian hasil dari rentang multi kolom atau multi baris secara langsung. Cukup dengan menentukan return_array sebagai rentang yang mencakup beberapa kolom atau baris, Excel akan secara otomatis menumpahkan (spill) hasil ke cell-cell berdekatan. Ini membuat rumus lebih ringkas, fleksibel, dan tahan terhadap perubahan struktur data.
=XLOOKUP(A9; A2:A6; B2:C6)
Penanganan Error Bawaan
Kasus: LOOKUP, VLOOKUP, dan HLOOKUP akan mengembalikan error #N/A jika nilai yang dicari tidak ditemukan dalam rentang pencarian. Untuk mencegahnya, pengguna biasanya membungkus rumus dengan IFERROR atau IFNA, yang menambah kompleksitas dan panjang rumus.
Solusi XLOOKUP: Fungsi XLOOKUP menyediakan penanganan error bawaan melalui parameter if_not_found. Jika nilai tidak ditemukan, Excel akan langsung menampilkan alternatif yang ditentukan—tanpa perlu fungsi tambahan.
Rumus berikut mengembalikan teks Tidak ada karena tidak menemukan nilai yang diinginkan.
=XLOOKUP(D2; A2:A6; B2:B6; "Tidak ada")
Pencarian Partial/Wildcard dengan Match Mode
Kasus: Fungsi-fungsi lookup lama memiliki keterbatasan dalam pencarian dengan wildcard. HLOOKUP dan VLOOKUP hanya dapat melakukan pencarian parsial saat range_lookup bernilai FALSE. LOOKUP hanya dapat melakukannya saat data terurut ascending.
Solusi XLOOKUP: XLOOKUP mendukung pencarian parsial dengan wildcard. Cara ini dilakukan dengan mengatur match_mode = 2.
Rumus berikut mencari produk yang memiliki an dan mengembalikan produk pertama yang cocok.
Pencarian dari Bawah atau Kanan
Kasus: Fungsi VLOOKUP dan HLOOKUP hanya mendukung pencarian satu arah: VLOOKUP ke kanan, HLOOKUP ke bawah. Jika data yang relevan berada di sebelah kiri atau di atas nilai pencarian, kedua fungsi ini tidak dapat mengaksesnya secara langsung.
Solusi XLOOKUP: Fungsi XLOOKUP mengatasi batasan arah pencarian dengan parameter search_mode. Dengan mengatur nilai search_mode ke -1, kita dapat melakukan pencarian dari bawah ke atas atau dari kanan ke kiri—tanpa perlu membalik data atau menulis rumus tambahan. Ini memberi fleksibilitas penuh dalam mengakses data, terlepas dari arah penyusunannya.
Rumus berikut mencari user yang login terakhir kali.
=XLOOKUP(E1; A2:A6; B2:B6; ; ; -1)
Pencarian Binari untuk Data Besar
Kasus: Pencarian data dalam jumlah besar—misalnya ribuan hingga ratusan ribu baris—dapat menyebabkan rumus seperti VLOOKUP atau XLOOKUP berjalan lambat, bahkan membuat Excel membeku jika tidak dioptimalkan.
Solusi: Fungsi XLOOKUP menyediakan mode pencarian binari melalui parameter search_mode. Jika data sudah diurutkan, pencarian binari dapat dilakukan dengan:
- search_mode = 2 untuk data terurut naik (ascending)
- search_mode = -2 untuk data terurut turun (descending)
Rumus di bawah mencari angka 45000 di 99000 baris data menggunakan pencarian binari.
=XLOOKUP(45000; A2:A100000; B2:B100000; "Tidak ketemu"; 1; 2)Pencarian dengan Beberapa Nilai Sekaligus dengan Array
Kasus: Secara bawaan, VLOOKUP dan HLOOKUP tidak dirancang untuk melakukan pencarian terhadap beberapa nilai sekaligus. Untuk mencari lebih dari satu nilai, pengguna harus menyalin rumus ke banyak cell atau membangun rumus array yang kompleks.
Solusi XLOOKUP: Fungsi XLOOKUP mendukung pencarian terhadap banyak nilai sekaligus menggunakan array sebagai lookup_value. Excel akan secara otomatis menumpahkan hasil ke cell-cell berdekatan, membuat rumus lebih ringkas, efisien, dan mudah dipelihara—tanpa perlu pengulangan manual atau rumus tambahan.
=XLOOKUP(D2:D3; A2:A5; B2:B5)
Kekurangan & Batasan Fungsi XLOOKUP yang Perlu Diketahui
Tidak Tersedia di Semua Versi Excel
XLOOKUP hanya tersedia di Excel 2019 ke atas, Excel Web, dan Excel 365. Excel versi lain akan menampilkan #NAME!.
Tidak Ada Dukungan Bawaan Bagi Multi Kriteria
XLOOKUP tidak memiliki dukungan bawaan terhadap penggunaan kriteria. Solusi kriteria dilakukan lewat simulasi logika Boolean.
Contoh:
=XLOOKUP(1; (A2="Jakarta")*(B2="Aktif"); C3)Tidak Bisa Mengembalikan Array Multi Dimensi
XLOOKUP dapat menerima array sebagai pencarian, tapi tidak dapat mengembalikan array multi dimensi.
=XLOOKUP(A3:A5; A2:A6; A2:C6)Rumus di atas hanya mengembalikan array A3:A5 alih-alih array dari A2:C6.
Hanya Mengembalikan Hasil Pertama yang Cocok
XLOOKUP hanya mengembalikan hasil pertama yang cocok dan mengabaikan sisanya.
Performa Memburuk di Rumus Bersarang dan Kompleks
Kombinasi XLOOKUP dengan fungsi-fungsi lain seperti SORTBY, IF, atau LET, pada data besar dapat membuat workbook berjalan lambat. Saat kondisi ini terjadi, kombinasi INDEX+MATCH bekerja lebih baik dari XLOOKUP.
Penangangan Kesalahan Terbatas pada Satu Error
Berbeda dengan fungsi IFERROR, if_not_found hanya menangani satu jenis kesalahan, yaitu #N/A. Jika rumus menghasilkan kesalahan lain secara bersamaan—seperti #VALUE! atau #REF!—maka XLOOKUP tetap akan mengembalikan error tersebut, karena if_not_found tidak dirancang untuk menangani jenis kesalahan selain ketidaksesuaian data.
Perbandingan Fungsi XLOOKUP dari Fungsi Lookup Lain
Tabel berikut merangkum bagaimana XLOOKUP mengatasi kelemahan-kelemahan pendahulunya sekaligus menawarkan fitur-fitur baru.
| Fitur | XLOOKUP | VLOOKUP / HLOOKUP / LOOKUP |
|---|---|---|
| Arah pencarian | Vertikal dan horizontal | VLOOKUP hanya vertikal HLOOKUP hanya horizontal |
| Tipe pencocokan | Exact match (default) | Approximate match (default) |
| Lookup ke kiri atau ke atas | Ya | VLOOKUP tidak bisa melihat ke kiri dan HLOOKUP tidak bisa ke kanan |
| Spill atau tumpahan array | Ya | Butuh rumus array |
| Penanganan kesalahan | Tersedia lewat parameter if_not_found | Membutuhkan IFNA atau IFERROR |
| Pencarian wildcard | Tersedia (match_mode = 2) | Terbatas atau tidak ada |
| Pencarian terbalik | Ya (search_mode = -1, 1) | Tidak |
| Pencarian binari | Ya (search_mode = 2, -2) | Tidak |
| Mengembalikan lebih dari satu kolom atau baris | Ya | Dengan bantuan array |
| Array dinamis | Ya | Tidak |
Kesimpulan
XLOOKUP bukan sekadar fungsi baru—tapi juga lompatan besar terhadap cara kita mencari dan mengolah data di Excel. Dengan sintaks yang lebih intuitif, pencarian dua arah, penanganan error bawaan, dan fleksibilitas tinggi, XLOOKUP mengatasi semua keterbatasan dari LOOKUP, VLOOKUP, dan HLOOKUP.
Jika teman-teman masih menggunakan fungsi pencarian lama, inilah saatnya beralih.