Ilustrasi: Simbol XLOOKUP
Bagi para pengguna Microsoft Excel, mencari dan mengambil data dari tabel atau rentang tertentu adalah tugas yang sangat umum. Selama bertahun-tahun, fungsi-fungsi seperti VLOOKUP dan HLOOKUP menjadi andalan. Namun, Microsoft telah memperkenalkan sebuah fungsi yang lebih kuat, fleksibel, dan mudah digunakan: XLOOKUP. Fungsi ini dirancang untuk menggantikan kedua fungsi lama tersebut dan menawarkan solusi yang lebih intuitif untuk berbagai skenario pencarian data.
Jika Anda baru mengenal XLOOKUP atau ingin memahami lebih dalam bagaimana cara menggunakannya, Anda berada di tempat yang tepat. Artikel ini akan membahas secara komprehensif cara XLOOKUP Excel, mulai dari sintaks dasarnya hingga contoh-contoh praktis yang akan membantu Anda menguasai fungsi canggih ini.
XLOOKUP memiliki sintaks yang relatif sederhana namun sangat kuat. Berikut adalah struktur dasarnya:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Mari kita bedah setiap argumennya:
lookup_value (Wajib): Nilai yang ingin Anda cari. Ini bisa berupa teks, angka, nilai boolean, atau referensi sel.lookup_array (Wajib): Rentang atau larik tempat Anda ingin mencari lookup_value. Penting untuk dicatat bahwa rentang ini hanya satu kolom atau satu baris.return_array (Wajib): Rentang atau larik tempat Anda ingin mengembalikan nilai yang sesuai. Rentang ini juga harus satu kolom atau satu baris, dan ukurannya harus sama dengan lookup_array.[if_not_found] (Opsional): Nilai yang akan dikembalikan jika lookup_value tidak ditemukan dalam lookup_array. Jika argumen ini dihilangkan dan nilai tidak ditemukan, maka fungsi akan mengembalikan error #N/A.[match_mode] (Opsional): Menentukan jenis pencocokan yang diinginkan. Pilihan yang tersedia adalah:
0 (Default): Pencocokan persis.-1: Pencocokan persis atau item terkecil berikutnya.1: Pencocokan persis atau item terbesar berikutnya.2: Pencocokan karakter wildcard (? untuk satu karakter, * untuk nol atau lebih karakter).[search_mode] (Opsional): Menentukan arah pencarian. Pilihan yang tersedia adalah:
1 (Default): Cari dari item pertama hingga terakhir.-1: Cari dari item terakhir hingga pertama (pencarian terbalik).2: Pencarian biner (memerlukan lookup_array diurutkan naik).-2: Pencarian biner (memerlukan lookup_array diurutkan turun).Sebelum XLOOKUP, VLOOKUP dan HLOOKUP memiliki beberapa keterbatasan yang membuatnya terkadang merepotkan. XLOOKUP hadir untuk mengatasi ini:
VLOOKUP hanya bisa mencari ke kanan, sedangkan HLOOKUP hanya mencari ke bawah. XLOOKUP dapat mencari ke kiri, kanan, atas, atau bawah.VLOOKUP, kolom data yang ingin dikembalikan harus berada di sebelah kanan kolom pencarian. XLOOKUP tidak memiliki batasan ini.if_not_found membuat penanganan error #N/A menjadi lebih mudah dan langsung terintegrasi dalam rumus.search_mode dengan nilai -1 memungkinkan pencarian dari akhir ke awal, sangat berguna jika ada duplikat dan Anda ingin hasil yang paling baru.XLOOKUP lebih mudah dibaca dan dipahami dibandingkan beberapa argumen tersembunyi pada VLOOKUP (seperti indeks kolom).Mari kita lihat beberapa skenario umum dan bagaimana XLOOKUP dapat digunakan:
Misalkan Anda memiliki tabel data seperti di bawah ini:
Tabel Produk (Sheet1)
| Kode Produk | Nama Produk | Harga |
|---|---|---|
| A101 | Laptop XYZ | 12000000 |
| B205 | Mouse Wireless | 150000 |
| C310 | Keyboard Mekanik | 850000 |
| A105 | Monitor 24" | 2500000 |
Di sel lain, Anda ingin mencari harga sebuah produk berdasarkan kode produknya. Jika Anda memasukkan "A101" di sel E2, Anda bisa menggunakan rumus berikut di sel F2 untuk mendapatkan nama produk:
=XLOOKUP(E2, Sheet1!A2:A5, Sheet1!B2:B5)
Dan untuk mendapatkan harganya di sel G2:
=XLOOKUP(E2, Sheet1!A2:A5, Sheet1!C2:C5)
Jika kode produk tidak ditemukan, misalnya Anda memasukkan "D999" di E2, maka hasil dari rumus di atas adalah #N/A.
Untuk menghindari error #N/A, kita bisa menggunakan argumen if_not_found:
=XLOOKUP(E2, Sheet1!A2:A5, Sheet1!C2:C5, "Produk Tidak Ditemukan")
Sekarang, jika kode produk "D999" dimasukkan, sel akan menampilkan teks "Produk Tidak Ditemukan".
Misalkan Anda memiliki data penjualan dengan tanggal, dan Anda ingin mencari penjualan terakhir untuk produk tertentu.
| Tanggal | Produk | Jumlah |
|---|---|---|
| 2023-10-01 | Laptop XYZ | 5 |
| 2023-10-02 | Mouse Wireless | 10 |
| 2023-10-02 | Laptop XYZ | 3 |
| 2023-10-03 | Keyboard Mekanik | 7 |
| 2023-10-03 | Laptop XYZ | 2 |
Jika Anda mencari "Laptop XYZ" dan ingin mengambil jumlah dari baris terakhir yang muncul untuk produk tersebut, Anda bisa gunakan:
=XLOOKUP("Laptop XYZ", B2:B6, C2:C6, "", 0, -1)
Di sini, -1 pada argumen search_mode menginstruksikan Excel untuk mencari dari bawah ke atas. Hasilnya adalah 2.
Jika Anda ingin mencari produk yang namanya dimulai dengan "Lap" (misalnya "Laptop XYZ" dan "Laptop ABC"), Anda bisa menggunakan wildcard:
=XLOOKUP("Lap*", A2:A5, B2:B5, "", 0, 1, 2)
Ini akan mengembalikan nama produk pertama yang cocok dengan pola "Lap*" di kolom A, yang dalam kasus ini adalah "Laptop XYZ".
Secara umum, XLOOKUP adalah pilihan terbaik untuk hampir semua skenario pencarian data di Excel. Ini adalah fungsi pengganti yang lebih baik dan lebih modern untuk VLOOKUP dan HLOOKUP. Gunakan XLOOKUP jika:
XLOOKUP tersedia di Microsoft 365, Excel 2021, dan versi yang lebih baru. Pastikan spreadsheet Anda menggunakan format file yang mendukung fungsi ini (seperti .xlsx).
Menguasai cara XLOOKUP Excel akan secara signifikan meningkatkan efisiensi Anda dalam mengelola dan menganalisis data. Cobalah rumus-rumus di atas pada data Anda sendiri dan rasakan perbedaannya!