Pakai XLOOKUP jika semua orang yang memakai file Anda punya Excel 2021, Microsoft 365, atau Google Sheets: rumusnya lebih pendek dan aman saat kolom bertambah. Pakai INDEX dan MATCH jika file harus jalan di Excel lama. Keduanya bisa mencari ke kiri, dan hasilnya sama untuk pencarian biasa.
Data contoh
Tabel produk di A1:D5.
| Kolom 1 | A (Kode) | B (Produk) | C (Harga, Rp) | D (Stok) |
|---|---|---|---|---|
| 1 | Kode | Produk | Harga | Stok |
| 2 | P001 | Laptop Asus | 8.500.000 | 14 |
| 3 | P002 | Mouse Logitech | 150.000 | 120 |
| 4 | P003 | Keyboard | 350.000 | 45 |
| 5 | P004 | Monitor 24 inci | 2.100.000 | 22 |
Pencarian dasar: harga produk P003
| Fungsi | Rumus | Hasil |
|---|---|---|
| XLOOKUP | =XLOOKUP("P003",A2:A5,C2:C5) | 350.000 |
| INDEX + MATCH | =INDEX(C2:C5,MATCH("P003",A2:A5,0)) | 350.000 |
| VLOOKUP | =VLOOKUP("P003",A2:D5,3,FALSE) | 350.000 |
Cara kerja INDEX dan MATCH: MATCH mencari posisi “P003” di A2:A5 dan menghasilkan 3. INDEX mengambil isi urutan ke-3 dari C2:C5, yaitu 350.000. Angka 0 pada MATCH berarti cocok persis. XLOOKUP melakukan dua langkah itu dalam satu fungsi dan secara bawaan mencocokkan persis. Detail argumen XLOOKUP ada di dokumentasi Microsoft.
Mencari ke kiri: kode dari nama produk
VLOOKUP hanya bisa mengambil dari kolom di kanan kolom pencarian. Mencari kode (kolom A) dari nama produk (kolom B) harus memakai salah satu dari ini.
| Fungsi | Rumus | Hasil |
|---|---|---|
| XLOOKUP | =XLOOKUP("Keyboard",B2:B5,A2:A5) | P003 |
| INDEX + MATCH | =INDEX(A2:A5,MATCH("Keyboard",B2:B5,0)) | P003 |
Jika tidak ketemu
| Fungsi | Rumus | Hasil |
|---|---|---|
| XLOOKUP | =XLOOKUP("P009",A2:A5,C2:C5,"Tidak ada") | Tidak ada |
| INDEX + MATCH | =IFNA(INDEX(C2:C5,MATCH("P009",A2:A5,0)),"Tidak ada") | Tidak ada |
XLOOKUP punya argumen keempat khusus untuk ini. Pada INDEX dan MATCH pakai IFNA, bukan IFERROR, supaya error lain yang menandakan kesalahan rumus tidak ikut tersembunyi.
Dua arah: stok produk P003
Cari baris berdasarkan kode dan kolom berdasarkan judul. Judul kolom di B1:D1, data di B2:D5.
| Fungsi | Rumus | Hasil |
|---|---|---|
| INDEX + MATCH | =INDEX(B2:D5,MATCH("P003",A2:A5,0),MATCH("Stok",B1:D1,0)) | 45 |
| XLOOKUP | =XLOOKUP("P003",A2:A5,XLOOKUP("Stok",B1:D1,B2:D5)) | 45 |
Keuntungan dibanding VLOOKUP: nomor kolom tidak ditulis manual (3). Jika kolom baru disisipkan di tabel, rumus tetap benar, sedangkan VLOOKUP dengan angka indeks bisa menunjuk kolom yang salah.
Mengembalikan beberapa kolom sekaligus (XLOOKUP)
=XLOOKUP("P003",A2:A5,B2:D5) menghasilkan Keyboard, 350.000, dan 45 dalam tiga sel berdampingan (menumpah). Hasil banyak kolom ini berjalan di Excel 2021 dan 365, dan menurut bantuan Google XLOOKUP di Sheets juga bisa mengembalikan seluruh baris atau kolom hasil. Rumus INDEX dan MATCH butuh satu rumus per kolom.
Pencocokan perkiraan: diskon bertingkat
Diskon menurut jumlah beli. Batas bawah di F2:F5, diskon di G2:G5.
| F (Minimal Qty) | G (Diskon) |
|---|---|
| 0 | 0% |
| 10 | 5% |
| 50 | 10% |
| 100 | 15% |
Untuk pembelian 60 unit, diskonnya 10%, yaitu batas 50 yang terdekat di bawah 60.
| Fungsi | Rumus | Hasil |
|---|---|---|
| XLOOKUP | =XLOOKUP(60,F2:F5,G2:G5,,-1) | 10% |
| INDEX + MATCH | =INDEX(G2:G5,MATCH(60,F2:F5,1)) | 10% |
Pada XLOOKUP, -1 berarti cocok persis, atau ambil nilai berikutnya yang lebih kecil. Argumen perkiraan tidak otomatis. Pada MATCH, 1 mensyaratkan daftar batas terurut naik, kalau tidak hasilnya bisa keliru.
Perbandingan
| Aspek | XLOOKUP | INDEX + MATCH |
|---|---|---|
| Panjang rumus | Pendek | Lebih panjang |
| Cari ke kiri | Ya | Ya |
| Pencocokan bawaan | Persis | Tergantung argumen ketiga MATCH |
| Pesan jika tidak ketemu | Argumen bawaan | Perlu IFNA |
| Cari dari bawah ke atas | Ya (argumen search_mode) | Perlu trik tambahan |
| Hasil banyak kolom | Ya | Tidak |
| Kompatibel Excel 2019 ke bawah | Tidak | Ya |
| Google Sheets | Ya | Ya |
| Mudah dipelajari pemula | Ya | Butuh waktu |
Soal kecepatan: pada tabel kecil sampai menengah, selisihnya tidak terasa. Pertimbangan versi Excel biasanya jauh lebih menentukan daripada kecepatan.
Verdict
- Semua pemakai file memakai Excel 2021, 365, atau Google Sheets: pakai XLOOKUP.
- File dikirim ke orang dengan Excel lama atau tidak diketahui versinya: pakai INDEX dan MATCH.
- Sudah lancar dengan VLOOKUP dan tabel tidak akan berubah: boleh dipertahankan, tetapi untuk tabel baru pilih salah satu dua di atas.
- Pembuat template yang dijual: INDEX dan MATCH lebih aman karena Anda tidak tahu versi Excel pembeli.
Tips tambahan
- Kunci rentang dengan
$saat rumus disalin:$A$2:$A$5. - Bersihkan data kunci. Spasi tersembunyi dan angka berformat teks adalah penyebab utama
#N/A. Lihat pesan error Excel. - Kode unik. Pencarian hanya mengambil hasil pertama. Jika kode ganda, tandai dulu dengan cara menandai duplikat.
- Pilihan dropdown sebagai kunci. Pasangkan dengan dropdown list supaya kode yang dicari selalu ada di daftar.
Rumus pencarian lain ada di rumus dasar Excel: HLOOKUP, XMATCH, dan LOOKUP.
Baca juga
- VLOOKUP #N/A padahal data ada: kalau hasil pencarian malah #N/A, mulai dari sini.