Tutorial rumus Excel • contoh yang bisa diuji
Cara Pakai XLOOKUP di Excel untuk Pemula: Pengganti VLOOKUP yang Lebih Simpel
Bayangkan kamu punya daftar harga produk di satu sheet dan daftar pesanan di sheet lain. Untuk mengisi harga tiap pesanan, kamu memakai VLOOKUP dan menulis=VLOOKUP(A2,$D$2:$F$100,3,FALSE). Rumusnya jalan, tetapi setiap kali kolom harga bergeser, angka 3 itu harus kamu hitung ulang. Dan ketika kamu justru ingin mencari ke arah kiri — misalnya mencari kode produk dari nama produk — VLOOKUP sama sekali tidak bisa.
Jawaban singkat: di Excel 365 dan Excel 2021 ada fungsi XLOOKUP yang menggantikan VLOOKUP dengan rumus lebih pendek: cukup sebut kolom yang dicari dan kolom hasilnya, tanpa nomor kolom. Ia bisa mencari ke kiri maupun ke kanan, dan kamu bisa mengatur sendiri pesan yang muncul saat data tidak ketemu. Di bawah ini ada contoh lengkap yang hasilnya sudah dihitung, bisa kamu praktikkan baris per baris.
Coba latihan dan lihat jawabannya ↓Konsep: apa yang dilakukan XLOOKUP?
XLOOKUP mencari sebuah nilai di satu kolom (atau baris), lalu mengambil nilai dari kolom lain pada posisi yang sama. Analoginya seperti mencari nama di daftar absensi, lalu melihat kolom keterangan di baris nama tersebut. Bedanya dengan VLOOKUP: kolom hasil tidak harus di kanan kolom pencarian, dan kamu tidak perlu menghitung nomor kolom.
Tiga bagian rumus yang perlu dipahami
=XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5)
- E2: nilai yang ingin dicari, misalnya kode produk.
- $A$2:$A$5: kolom tempat Excel mencari nilai tersebut. Tanda dolar menjaga range saat rumus disalin.
- $C$2:$C$5: kolom hasil yang diambil dari baris yang cocok.
Ada argumen keempat yang opsional, yaitu pesan pengganti saat nilai tidak ditemukan:=XLOOKUP(E3,$A$2:$A$5,$C$2:$C$5,"Kode tidak terdaftar"). Jika kode tidak ada, cell menampilkan Kode tidak terdaftaralih-alih #N/A.
Praktik: mengisi harga dan mencari kode ke arah kiri
Buat workbook baru. Isi tabel master produk di kolom A sampai C, mulai dari baris pertama sebagai header:
| Baris | Kode (A) | Nama (B) | Harga (C) |
|---|---|---|---|
| 2 | P-101 | Kopi Arabika 200g | 85000 |
| 3 | P-102 | Kopi Robusta 200g | 72000 |
| 4 | P-103 | Gula Aren 500g | 35000 |
| 5 | P-104 | Susu Oat 1L | 45000 |
Berikutnya, buat area pencarian di kolom E dan F. Tulis Dicari pada E1 dan Hasil pada F1, lalu masukkan empat kasus berikut:
| Cell E | Isi | Rumus pada F | Hasil F yang diharapkan |
|---|---|---|---|
| E2 | P-103 | =XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5) | 35000 |
| E3 | P-999 | =XLOOKUP(E3,$A$2:$A$5,$C$2:$C$5,"Kode tidak terdaftar") | Kode tidak terdaftar |
| E4 | Susu Oat 1L | =XLOOKUP(E4,$B$2:$B$5,$A$2:$A$5) | P-104 |
| E5 | P-102 | =VLOOKUP(E5,$A$2:$C$5,3,FALSE) | 72000 |
- Isi F2 dengan rumus baris pertama. Excel mencari P-103 di kolom A, menemukannya di baris 4, lalu mengambil harga dari kolom C baris yang sama: 35000.
- Isi F3. Karena P-999 tidak ada di kolom A, argumen keempat menggantikan #N/A dengan Kode tidak terdaftar.
- Isi F4. Ini pencarian ke kiri: Excel mencari nama di kolom B, menemukannya di baris 5, lalu mengambil kode dari kolom A: P-104. VLOOKUP tidak bisa melakukan ini karena kolom hasil harus di kanan kolom pencarian.
- Isi F5 dengan VLOOKUP untuk perbandingan. Hasilnya sama, 72000, tetapi kamu harus menghitung sendiri bahwa harga adalah kolom ke-3 dalam range A:C.
Catatan pengaturan Excel: jika rumus dengan koma tidak diterima, gunakan titik koma sebagai pemisah argumen, misalnya=XLOOKUP(E2;$A$2:$A$5;$C$2:$C$5;"Kode tidak terdaftar"). Tergantung pengaturan wilayah Windows, Excel Indonesia umumnya memakai titik koma.
Empat error umum dan cara memeriksanya
1. #NAME? — Excel kamu mungkin terlalu lama
Ini penyebab paling umum bagi pemula: XLOOKUP hanya tersedia di Microsoft 365 dan Excel 2021 ke atas. Di Excel 2019, 2016, atau lebih lama, Excel tidak mengenal nama fungsi ini dan menampilkan #NAME?. Cek versimu lewat File → Account → About Excel. Jika versimu lama, tetap gunakan VLOOKUP untuk kasus ini — rumusnya memang lebih panjang, tetapi tersedia di semua versi.
2. #N/A — nilai memang tidak ditemukan
Tanpa argumen keempat, XLOOKUP menampilkan #N/A saat nilai pencarian tidak ada di kolom sumber, sama seperti VLOOKUP. Bedanya, kamu bisa langsung menulis pesan pengganti di dalam rumus yang sama. Tetap periksa ejaan dan spasi berlebih pada nilai pencarian, karena P-103 danP-103 (ada spasi di akhir) dianggap berbeda.
3. #VALUE! — dua range tidak sejajar
Kolom pencarian dan kolom hasil harus mencakup jumlah baris yang sama. $A$2:$A$5 dipasangkan dengan $C$2:$C$6 akan error karena barisnya tidak seimbang. Saat menambah data master, perpanjang kedua range bersama-sama.
4. Hasil salah tanpa error — tipe data tercampur
Kode angka 103 tidak sama dengan kode teks'103 walau terlihat mirip di layar. XLOOKUP melakukan pencocokan persis secara default, jadi perbedaan tipe membuat pencarian gagal. Cek dengan=ISNUMBER(E2) di kedua tabel sebelum mengubah apa pun.
Kapan tetap memakai VLOOKUP?
XLOOKUP lebih simpel, tetapi bukan berarti VLOOKUP usang. Jika file kerjamu dibuka di komputer kantor dengan Excel 2016 atau 2019, rumus XLOOKUP akan rusak di sana. Untuk file yang dibagikan ke banyak orang dengan versi Excel berbeda, VLOOKUP masih pilihan yang aman. Dan jika VLOOKUP-mu menampilkan #N/A padahal datanya terlihat ada, masalahnya biasanya bukan pada pilihan fungsi — baca panduan memperbaiki VLOOKUP #N/A untuk memeriksa tipe data, spasi, dan exact match.
Uji mandiri: satu produk baru, dua arah pencarian
Tambahkan baris keenam di tabel master: kode P-105, nama Teh Melati 1 pack, harga 28000. Perpanjang range pada rumus menjadi $A$2:$A$6 dan $C$2:$C$6.
- Cari kode P-105 dengan XLOOKUP ke kolom harga. Hasil yang benar adalah 28000.
- Cari nama Teh Melati 1 pack dengan XLOOKUP ke kolom kode. Hasil yang benar adalah P-105. Perhatikan bahwa arah pencarian ini tidak bisa ditiru VLOOKUP.
- Sengaja cari kode P-000 dengan argumen keempat berisi pesan versimu sendiri, lalu pastikan pesan itu yang muncul.
Ingin latihan dengan masalah Excel lain? Kembali ke hub tutorial Excel atau pelajari cara membuat rekap penjualan Excel. Kalau konsep cell dan range masih membingungkan, mulai dari panduan Excel pemula.