VLOOKUP menghasilkan #N/A padahal datanya ada karena nilai yang dicari tidak sama persis dengan isi sel di tabel. Penyebab tersering: spasi tersembunyi, angka yang tersimpan sebagai teks, argumen keempat FALSE yang terlewat, kolom kunci bukan paling kiri, atau rentang yang bergeser. Tiap penyebab punya rumus uji dan perbaikan di bawah.
Tabel diagnosis cepat
| # | Penyebab | Rumus uji | Tanda | Perbaikan |
|---|---|---|---|---|
| 1 | Spasi tersembunyi | =LEN(E2) lawan =LEN(TRIM(E2)) | Angka berbeda | TRIM(E2) |
| 2 | Angka tersimpan sebagai teks | =ISNUMBER(E2) | FALSE | VALUE(E2) |
| 3 | Argumen keempat FALSE terlewat | Lihat akhir rumus | Hasil salah atau #N/A | Tambahkan FALSE |
| 4 | Kolom kunci bukan paling kiri | =MATCH(E2,A2:A6,0) | #N/A | Mulai rentang dari kolom kunci |
| 5 | Rentang bergeser | =FORMULATEXT(E4) | Rentang tidak tetap | Kunci dengan $ |
| 6 | Huruf atau karakter berbeda, termasuk non-breaking space | =CODE(RIGHT(E2,1)), =EXACT(E2,A3) | Kode 160, atau EXACT FALSE | SUBSTITUTE, ketik ulang |
| 7 | Pencocokan perkiraan pada data tak urut | =A3>=A2 disalin ke bawah | Ada FALSE | Pakai FALSE, atau urutkan A ke Z |
Menurut halaman VLOOKUP di Microsoft Support, #N/A dengan FALSE berarti tidak ada kecocokan persis. Jadi tugas diagnosisnya satu: cari tahu kenapa dua nilai yang tampak sama ternyata tidak sama.
Data contoh
Tabel harga di A1:C6. Baris 1 berisi judul.
| Kolom 1 | A (Kode) | B (Barang) | C (Harga) |
|---|---|---|---|
| 2 | BK-01 | Buku tulis 38 lembar | 4.500 |
| 3 | PL-02 | Pulpen gel | 3.500 |
| 4 | PN-03 | Pensil 2B | 2.000 |
| 5 | PR-04 | Penghapus | 1.500 |
| 6 | ST-05 | Stapler kecil | 12.000 |
Rumus dasarnya =VLOOKUP(E2,$A$2:$C$6,3,FALSE). Contoh memakai koma sebagai pemisah argumen. Jika pengaturan regional Anda memakai koma desimal, pakai titik koma.
1. Spasi tersembunyi
Kode diketik atau disalin dengan spasi di ujung: PL-02 + spasi.
| Input E2 | Rumus | Hasil |
|---|---|---|
PL-02 (ada spasi) | =VLOOKUP(E2,$A$2:$C$6,3,FALSE) | #N/A |
=LEN(E2) | 6 | |
=LEN(TRIM(E2)) | 5 | |
=VLOOKUP(TRIM(E2),$A$2:$C$6,3,FALSE) | 3.500 |
Kode yang tampak 5 karakter ternyata 6. Itu tanda ada spasi. TRIM membuang spasi di awal dan akhir, serta menyisakan satu spasi di antara kata. Jika spasi ada di tabel sumber (kolom A), bersihkan di sana, atau gunakan kolom pembantu =TRIM(A2) lalu salin sebagai nilai.
VLOOKUP gagal karena spasi di ujung kunci
Kunci PL-02 diketik dengan satu spasi di belakangnya, jadi VLOOKUP biasa menghasilkan #N/A.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Kode | Barang | Harga | Kunci | VLOOKUP | LEN | LEN(TRIM) | VLOOKUP + TRIM | |
| 2 | BK-01 | Buku tulis 38 lembar | Rp 4.500 | PL-02 | #N/A | 6 | 5 | Rp 3.500 | |
| 3 | PL-02 | Pulpen gel | Rp 3.500 | ||||||
| 4 | PN-03 | Pensil 2B | Rp 2.000 | ||||||
| 5 | PR-04 | Penghapus | Rp 1.500 | ||||||
| 6 | ST-05 | Stapler kecil | Rp 12.000 |
Sel biru E2 bisa diubah. Hapus spasi di ujung kunci, atau ketik PL-O2 dengan huruf O, lalu lihat kolom F sampai I berubah.
Panah, Enter, dan Tab memindah sel. Esc mengembalikan sel ke isi awal.
Pelajaran
=LEN(E2)
Kode tampak lima karakter, tetapi hasilnya enam. Selisih itu tanda ada spasi.
=VLOOKUP(TRIM(E2),A2:C6,3,FALSE)
TRIM membuang spasi di ujung, jadi kunci cocok dengan PL-02 di tabel.
2. Angka tersimpan sebagai teks
Kode barang numerik sering datang dari ekspor sebagai teks. Tabel B:
| Kode | Barang | Harga |
|---|---|---|
| 1001 | Gula 1 kg | 17.500 |
| 1002 | Minyak goreng 1 L | 18.000 |
| 1003 | Tepung 1 kg | 12.000 |
Kunci yang dicari adalah teks "1002" di sel E3, sedangkan kolom Kode berisi angka 1002.
| Rumus | Hasil |
|---|---|
=ISNUMBER(E3) | FALSE |
=VLOOKUP(E3,tabel,3,FALSE) | #N/A |
=VLOOKUP(VALUE(E3),tabel,3,FALSE) | 18.000 |
tabel mewakili rentang tabel B. Tanda lain: sel rata kiri, atau muncul segitiga hijau kecil di pojok sel. Kebalikannya juga bisa terjadi: tabel berisi teks, kunci berisi angka. Untuk itu ubah kunci menjadi teks dengan E3&"". VALUE mengubah teks angka menjadi angka. ISNUMBER menjadi pengujinya. Arti error lain yang sering menyertainya ada di pesan error Excel.
3. Argumen keempat FALSE terlewat
Tanpa argumen keempat, VLOOKUP memakai pencocokan perkiraan (TRUE). Menurut Microsoft dan Google, mode ini mengandaikan kolom pertama terurut. Bahayanya: kode yang tidak ada bisa tetap mendapat hasil, yaitu baris di atasnya.
| Kunci | Rumus | Hasil |
|---|---|---|
PM-09 (tidak ada di tabel A) | =VLOOKUP("PM-09",$A$2:$C$6,3) | 3.500 (harga PL-02, salah) |
PM-09 | =VLOOKUP("PM-09",$A$2:$C$6,3,FALSE) | #N/A |
AK-01 (lebih kecil dari semua kode) | =VLOOKUP("AK-01",$A$2:$C$6,3) | #N/A |
PN-03 (ada) | =VLOOKUP("PN-03",$A$2:$C$6,3) | 2.000 |
Contoh pertama berbahaya karena tidak muncul error. Harga yang salah tampak normal. Biasakan menulis argumen keempat untuk setiap pencarian kode atau nama. Pakai TRUE hanya untuk tabel rentang, seperti tarif berjenjang atau konversi nilai ke huruf.
VLOOKUP tanpa FALSE memberi harga barang lain
Kunci PM-09 tidak ada di tabel. Tanpa argumen keempat, VLOOKUP tetap memberi harga baris di atasnya.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Kode | Barang | Harga | Kunci | Tanpa FALSE | Dengan FALSE | Panjang kunci | |
| 2 | BK-01 | Buku tulis 38 lembar | Rp 4.500 | PM-09 | Rp 3.500 | #N/A | 5 | |
| 3 | PL-02 | Pulpen gel | Rp 3.500 | |||||
| 4 | PN-03 | Pensil 2B | Rp 2.000 | |||||
| 5 | PR-04 | Penghapus | Rp 1.500 | |||||
| 6 | ST-05 | Stapler kecil | Rp 12.000 |
Sel biru E2 bisa diubah. Coba AK-01, PN-03, atau PM-09 dan bandingkan kolom F dengan G.
Panah, Enter, dan Tab memindah sel. Esc mengembalikan sel ke isi awal.
Pelajaran
=VLOOKUP(E2,A2:C6,3)
Tanpa FALSE, kode yang tidak ada mendapat harga baris di atasnya, dan tidak ada error yang memberi tahu.
4. Kolom kunci bukan paling kiri
VLOOKUP hanya mencari di kolom pertama rentang. Tabel C punya susunan Barang di kolom A, Kode di kolom B, Harga di kolom C:
| A (Barang) | B (Kode) | C (Harga) |
|---|---|---|
| Pulpen gel | PL-02 | 3.500 |
| Pensil 2B | PN-03 | 2.000 |
| Rumus | Hasil |
|---|---|
=MATCH("PL-02",A2:A3,0) | #N/A |
=VLOOKUP("PL-02",A2:C3,3,FALSE) | #N/A |
=VLOOKUP("PL-02",B2:C3,2,FALSE) | 3.500 |
Mulai rentang dari kolom kunci, lalu hitung nomor kolom dari sana: Harga adalah kolom ke-2 dari B. Jika data tidak bisa dipindah, pakai INDEX dan MATCH atau XLOOKUP yang bisa mencari ke kiri (perbandingannya).
5. Rentang bergeser
Rumus ditulis =VLOOKUP(D2,A2:C6,3,FALSE) tanpa $, lalu disalin ke bawah. Rentang ikut turun satu baris di setiap salinan. Kunci di D2 sampai D5 adalah PL-02, ST-05, BK-01, PN-03.
| Sel | Rumus hasil salinan | Hasil |
|---|---|---|
| E2 | =VLOOKUP(D2,A2:C6,3,FALSE) | 3.500 |
| E3 | =VLOOKUP(D3,A3:C7,3,FALSE) | 12.000 |
| E4 | =VLOOKUP(D4,A4:C8,3,FALSE) | #N/A |
| E5 | =VLOOKUP(D5,A5:C9,3,FALSE) | #N/A |
BK-01 ada di baris 2 dan PN-03 di baris 4, keduanya di atas rentang yang sudah bergeser. Cek dengan =FORMULATEXT(E4) untuk melihat rumus sebagai teks. Perbaikannya =VLOOKUP(D2,$A$2:$C$6,3,FALSE), yang menghasilkan 3.500, 12.000, 4.500, dan 2.000. Tips: di Excel Windows, tekan F4 setelah memilih rentang untuk menambah tanda $.
6. Huruf atau karakter berbeda, termasuk non-breaking space
Ini penyebab yang paling licin, karena sel tampak identik. Tiga kasusnya:
| Kunci | Masalah | Rumus uji | Hasil uji |
|---|---|---|---|
PL-02 + non-breaking space | Spasi kode 160 di ujung | =CODE(RIGHT(E6,1)) | 160 |
PL-O2 | Huruf O, bukan angka 0 | =CODE(MID(E7,4,1)) | 79 (angka 0 = 48) |
PLâ02 | Tanda pisah panjang, bukan hubung | =UNICODE(MID(E8,3,1)) | 8211 (tanda hubung = 45) |
=EXACT(E7,A3) memberi FALSE. Ketiganya membuat VLOOKUP menghasilkan #N/A.
Perhatikan bahwa TRIM tidak menghapus non-breaking space. Microsoft menyatakan bahwa TRIM dirancang untuk spasi ASCII 32 saja (TRIM, Microsoft Support). Ganti dulu karakternya dengan SUBSTITUTE, lalu bersihkan sisanya:
=TRIM(SUBSTITUTE(E6,UNICHAR(160)," "))
Rumus ini mengubah kode 160 menjadi spasi biasa, lalu TRIM membuangnya. Untuk huruf O atau tanda pisah panjang, ketik ulang atau ganti dengan SUBSTITUTE. CLEAN membuang karakter yang tidak tercetak, tetapi tidak membuang kode 160. Fungsi CODE dan EXACT menjadi alat uji.
7. Pencocokan perkiraan pada data tak urut
Jika memang memakai TRUE (atau tanpa argumen keempat), kolom kunci harus urut naik. Tabel D di bawah belum urut:
| A (Kode) | Uji di baris ini | Hasil |
|---|---|---|
| ST-05 | ||
| BK-01 | =A3>=A2 | FALSE |
| PN-03 | =A4>=A3 | TRUE |
| PL-02 | =A5>=A4 | FALSE |
| PR-04 | =A6>=A5 | TRUE |
Ada FALSE, jadi data tidak urut dan hasil perkiraan tidak bisa dipercaya. Google menjelaskan bahwa Sheets membaca dari atas sampai menemukan nilai lebih besar dari kunci, lalu mengambil baris di atasnya (Bantuan Editor Google Dokumen). Pada data seperti ini hasilnya tidak bisa diandalkan: bisa #N/A, bisa harga barang lain, dan Excel pun tidak menjamin hasil pada data tak urut. Perbaikan: tambahkan FALSE, atau urutkan kolom A dari A ke Z.
Menangani #N/A yang memang wajar
Jika kode memang kadang tidak ada di tabel, tampilkan pesan yang jelas dengan IFNA:
=IFNA(VLOOKUP(TRIM(E2),$A$2:$C$6,3,FALSE),"Kode tidak ditemukan")
IFNA hanya menangkap #N/A, tidak error lain seperti #REF!. Ini lebih aman daripada IFERROR, yang menutup semua error. Jangan pasang IFNA sebelum Anda mengecek tujuh penyebab di atas, karena ia menyembunyikan masalah data yang sebenarnya. Menurut Microsoft, IFNA tersedia di Excel 2016 ke atas.
XLOOKUP sebagai alternatif
XLOOKUP mencocokkan persis secara bawaan, tidak butuh nomor kolom, dan bisa mencari ke kiri:
=XLOOKUP(TRIM(E2),$A$2:$A$6,$C$2:$C$6,"Kode tidak ditemukan")
Hasilnya 3.500 untuk PL-02 . XLOOKUP tersedia di Excel 365, Excel 2021, dan Excel 2024, tetapi tidak di Excel 2016 dan 2019 (XLOOKUP, Microsoft Support). Google Sheets juga punya XLOOKUP (Bantuan Editor Google Dokumen). Penyebab 1, 2, dan 6 tetap membuat XLOOKUP gagal, karena akarnya ada di data. Pilihan lengkapnya ada di XLOOKUP vs INDEX MATCH.
Beda Excel dan Google Sheets
| Hal | Excel | Google Sheets |
|---|---|---|
| Argumen keempat | range_lookup, bawaan TRUE | is_sorted, bawaan TRUE |
| Spasi kode 160 | Ganti dengan SUBSTITUTE dan UNICHAR(160) | Cara yang sama |
| IFNA, XLOOKUP | IFNA di Excel 2016+, XLOOKUP di 2021, 2024, 365 | Keduanya tersedia |
| Pemisah argumen | Koma atau titik koma (regional) | Sesuai bahasa dan wilayah file |
Kesalahan yang sering terjadi
- Langsung membungkus dengan IFNA. Error hilang, tetapi data yang salah tetap salah.
- Salah menghitung nomor kolom. Nomor dihitung dari kolom pertama rentang, bukan dari kolom A lembar.
- Tabel memuat kunci ganda. VLOOKUP hanya mengembalikan kemunculan pertama.
- Membersihkan kunci tetapi tidak tabel. Spasi bisa ada di kedua sisi. Bersihkan keduanya.
- Mengira tanda hubung dan tanda pisah itu sama. Data dari dokumen Word sering memakai tanda pisah.
Hasil dalam artikel ini dihitung ulang dengan skrip dan penalaran dari aturan VLOOKUP yang tertulis di dokumentasi, belum diuji langsung di Excel atau Google Sheets. Cobalah di data Anda sendiri sebelum dipakai. Fungsi yang dipakai: VLOOKUP, TRIM, VALUE, LEN, ISNUMBER, SUBSTITUTE, IFNA, MATCH, dan XLOOKUP.