Fungsi XLOOKUP di Excel tidak hanya menggantikan VLOOKUP dan HLOOKUP, tetapi juga menawarkan fleksibilitas yang jauh lebih besar dengan berbagai mode pencarian. Dalam tutorial ini, Anda akan mempelajari tiga mode pencarian utama XLOOKUP: Exact Match, Wildcard, dan Approximate Match.
Dengan memahami perbedaan dan penggunaan masing-masing mode, Anda akan bisa membuat rumus XLOOKUP yang lebih efisien dan sesuai dengan kebutuhan data Anda. Mari kita pelajari satu per satu!
Daftar Isi Tutorial
1. Apa Itu Parameter match_mode pada XLOOKUP?
Parameter match_mode adalah parameter ke-5 dalam fungsi XLOOKUP yang menentukan bagaimana pencarian dilakukan. Parameter ini opsional dan default-nya adalah 0 (exact match).
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
// match_mode: menentukan jenis pencarian (exact, wildcard, approximate)
Exact Match (0)
Mencari nilai yang persis sama dengan lookup_value. Mengembalikan #N/A jika tidak ditemukan.
Wildcard Match (2)
Menggunakan karakter wildcard (*, ?) untuk pencarian parsial. Sangat berguna untuk pencarian teks fleksibel.
Approximate Match (-1, 1)
Mencari nilai terdekat ketika exact match tidak ditemukan. Data harus diurutkan.
2. Exact Match (Mode 0) - Pencarian Tepat
Exact match adalah mode default XLOOKUP yang mencari nilai yang persis sama dengan lookup_value. Mode ini paling sering digunakan dalam praktik sehari-hari.
=XLOOKUP("B001", A2:A100, B2:B100)
// Sama dengan:
=XLOOKUP("B001", A2:A100, B2:B100, , 0)
// Mencari "B001" yang persis sama di kolom A
Karakteristik Exact Match:
- Case-insensitive: Tidak membedakan huruf besar/kecil
- Format-sensitive: Membedakan angka vs teks ("100" ≠ 100)
- Mengembalikan #N/A: Jika nilai tidak ditemukan
- Tidak perlu data terurut: Bisa digunakan pada data acak
3. Wildcard Match (Mode 2) - Pencarian Parsial
Wildcard match memungkinkan pencarian dengan karakter khusus untuk menemukan nilai yang mengandung teks tertentu atau mengikuti pola tertentu.
=XLOOKUP("*laptop*", A2:A100, B2:B100, , 2)
// Mencari yang diawali "APP"
=XLOOKUP("APP*", A2:A100, B2:B100, , 2)
// Mencari dengan pola 3 karakter setelah "ID-"
=XLOOKUP("ID-???", A2:A100, B2:B100, , 2)
Karakter Wildcard:
| Karakter | Deskripsi | Contoh | Hasil |
|---|---|---|---|
| * | Beberapa karakter (0 atau lebih) | "*laptop*" | "Gaming laptop", "Laptop Dell" |
| ? | Satu karakter (tepat satu) | "PRD-???" | "PRD-001", "PRD-ABC" |
| ~ | Escape character | "*~*" | Teks yang mengandung "*" |
Contoh Praktis Wildcard Match
4. Approximate Match (Mode -1 dan 1)
Approximate match digunakan ketika Anda ingin mencari nilai terdekat ketika exact match tidak ditemukan. Mode ini membutuhkan data yang sudah diurutkan.
=XLOOKUP(85, A2:A100, B2:B100, , -1)
// Mengembalikan nilai untuk angka ≤ 85
// Mencari nilai terdekat (lebih besar atau sama)
=XLOOKUP(85, A2:A100, B2:B100, , 1)
// Mengembalikan nilai untuk angka ≥ 85
Perbedaan Mode -1 dan 1:
Mode -1 (Exact or Next Smaller)
- Mencari exact match terlebih dahulu
- Jika tidak ditemukan, ambil nilai terbesar yang ≤ lookup_value
- Data harus diurutkan ascending
- Cocok untuk kategori harga, grade nilai
Mode 1 (Exact or Next Larger)
- Mencari exact match terlebih dahulu
- Jika tidak ditemukan, ambil nilai terkecil yang ≥ lookup_value
- Data harus diurutkan ascending
- Cocok untuk batas minimum, threshold
5. Binary Search untuk Data Terurut
XLOOKUP juga mendukung binary search yang jauh lebih cepat untuk data terurut yang besar melalui parameter search_mode.
=XLOOKUP(85, A2:A100, B2:B100, , -1, 2)
// Lebih cepat untuk data terurut besar
// Binary search descending (data terurut turun)
=XLOOKUP(85, A2:A100, B2:B100, , 1, -2)
// Untuk data terurut descending
Keuntungan Binary Search
- Lebih cepat: Kompleksitas O(log n) vs O(n) untuk linear search
- Efisien untuk data besar: Perbedaan kecepatan signifikan untuk ribuan baris
- Kebutuhan khusus: Data harus benar-benar terurut
- Search modes: 2 (ascending), -2 (descending), 1 (first to last), -1 (last to first)
6. Perbandingan Semua Mode Pencarian
Berikut adalah perbandingan lengkap semua mode pencarian XLOOKUP untuk membantu Anda memilih yang tepat untuk kebutuhan Anda.
| Match Mode | Nilai | Kegunaan | Data Terurut? | Contoh |
|---|---|---|---|---|
| Exact Match | 0 (default) | Pencarian nilai persis | Tidak perlu | =XLOOKUP("A001", ...) |
| Wildcard Match | 2 | Pencarian parsial dengan * ? | Tidak perlu | =XLOOKUP("*laptop*", ..., , 2) |
| Approximate (smaller) | -1 | Nilai ≤ lookup_value | Harus ascending | =XLOOKUP(85, ..., , -1) |
| Approximate (larger) | 1 | Nilai ≥ lookup_value | Harus ascending | =XLOOKUP(85, ..., , 1) |
Panduan Memilih Match Mode
- Exact (0): Untuk pencarian data referensi
- Wildcard (2): Untuk pencarian teks fleksibel
- Approx (-1): Untuk kategori (grade, tier)
- Approx (1): Untuk batas minimum
Pilih berdasarkan jenis data dan kebutuhan pencarian!
7. Contoh Kasus Praktis
Mari kita lihat contoh praktis penggunaan masing-masing mode pencarian dalam skenario nyata.
Contoh 1: Pencarian Produk (Exact Match)
=XLOOKUP(D2, A2:A100, B2:B100, "Produk tidak ditemukan")
// Mencari harga produk berdasarkan kode
Contoh 2: Pencarian Fleksibel (Wildcard)
=XLOOKUP("*Gaming*", A2:A100, B2:B100, "Tidak ada", 2)
// Menemukan "Laptop Gaming", "Mouse Gaming", dll.
Contoh 3: Kategori Nilai (Approximate Match)
=XLOOKUP(E2, G2:G6, H2:H6, , -1)
// Nilai 85 → Grade "B" (karena 80 ≤ 85 < 90)
8. Download Template Praktik
Untuk membantu Anda mempraktikkan semua mode pencarian XLOOKUP, kami menyediakan template Excel gratis yang berisi:
- Contoh data untuk setiap mode pencarian
- Rumus-rumus siap pakai dengan penjelasan
- Worksheet latihan interaktif
- Cheat sheet mode pencarian XLOOKUP
Yang Anda Dapatkan dalam Template
📊 Exact Match
Contoh pencarian data persis dengan kode produk
🌟 Wildcard
Pencarian fleksibel dengan berbagai pola wildcard
≈ Approximate
Kategori nilai, grade produk, dan tier harga
⚡ Binary Search
Perbandingan kecepatan dengan data besar
Pertanyaan yang Sering Diajukan (FAQ)
Exact match (mode 0) mencari nilai yang persis sama, sedangkan wildcard (mode 2) memungkinkan pencarian dengan karakter khusus * dan ? untuk pencarian parsial.
Gunakan match_mode 2 dan sertakan karakter * (untuk beberapa karakter) atau ? (untuk satu karakter) dalam lookup_value.
Approximate match (mode -1 atau 1) digunakan ketika data lookup_array diurutkan dan Anda ingin mencari nilai terdekat, seperti dalam pencarian kategori harga atau grade.
Ya, XLOOKUP mendukung binary search dengan match_mode -1 atau 1 dan search_mode 2, yang lebih cepat untuk data terurut yang besar.