Tutorial troubleshooting Excel • contoh yang bisa diuji
VLOOKUP #N/A Padahal Datanya Ada? Kenali Penyebabnya dan Cara Memperbaiki
Bayangkan kamu diminta melengkapi departemen puluhan pegawai dari tabel master. Kode pegawainya terlihat sama, tetapi saat memakai VLOOKUP, sebagian baris justru menampilkan #N/A. Kamu sudah mencoba mengganti rumus berkali-kali. Padahal bisa jadi rumusnya tidak salah: cara Excel membaca kode di dua tabel itu berbeda.
Jawaban singkat: untuk pencarian persis, pastikan kode benar-benar tersedia di kolom pertama tabel referensi, kedua sisi punya jenis data yang sama, tidak ada spasi tersembunyi, dan argumen terakhir VLOOKUP menggunakan FALSE. Di bawah ini ada contoh yang sengaja menghasilkan #N/A, lalu kita perbaiki.
Kenapa VLOOKUP bisa menampilkan #N/A?
VLOOKUP bertugas mencari sebuah nilai di kolom pertamadari tabel referensi, lalu mengambil informasi dari kolom lain pada baris yang sama. Bayangkan mencari nomor pegawai dalam buku daftar: kalau nomor yang dicari tidak cocok dengan daftar, Excel tidak tahu baris mana yang harus diambil.
Saat argumen terakhirnya FALSE, Excel mencari kecocokan persis. #N/A berarti nilai yang diminta tidak ditemukan dengan aturan pencarian tersebut. Ini bukan otomatis berarti datanya benar-benar hilang. Kode berupa angka 1002 dan teks '1002, misalnya, dapat terlihat serupa di layar tetapi tidak cocok saat dicari.
Empat bagian rumus yang perlu dipahami
=VLOOKUP(E2,$A$2:$C$4,3,FALSE)
- E2: kode yang ingin dicari.
- $A$2:$C$4: tabel master; kolom A adalah tempat pencarian kode. Tanda dolar menjaga range saat rumus disalin.
- 3: ambil hasil dari kolom ketiga dalam range A:C, yaitu departemen.
- FALSE: cari kecocokan persis, bukan perkiraan.
Latihan: kode pegawai yang terlihat benar tetapi gagal ditemukan
Buat workbook baru. Isi tabel master di kolom A sampai C, mulai dari baris pertama sebagai header. Kode di kolom A harus dimasukkan sebagai angka (tanpa tanda petik).
| Baris | Kode (A) | Nama (B) | Departemen (C) |
|---|---|---|---|
| 2 | 1001 | Dita | Admin |
| 3 | 1002 | Bima | Gudang |
| 4 | 1003 | Citra | Sales |
Berikutnya, buat area pencarian di kolom E dan F. Tulis Kode dicari pada E1 dan Departemenpada F1. Masukkan tiga variasi kode berikut:
| Cell E | Cara memasukkan | Hasil F yang diharapkan |
|---|---|---|
| E2 | 1002 sebagai angka | Gudang |
| E3 | '1002 (diawali tanda petik, menjadi teks) | #N/A |
| E4 | 9999 sebagai angka (tidak ada di master) | #N/A |
- Di F2 masukkan
=VLOOKUP(E2,$A$2:$C$4,3,FALSE). - Salin F2 sampai F4. Hasil berurutan seharusnya Gudang, #N/A, #N/A.
- Periksa jenis nilai dengan
=ISNUMBER(E2)dan=ISNUMBER(E3)pada dua cell kosong: hasilnya TRUE dan FALSE. - Pada cell kosong, coba
=VLOOKUP(VALUE(E3),$A$2:$C$4,3,FALSE). Hasilnya semestinya Gudang, sebab contoh ini memang memakai kode numerik. - Untuk E4, jangan memaksa hasil. Jika kode 9999 tidak ada di master, #N/A justru memberi tahu bahwa data referensi belum memuat kode tersebut.
Catatan pengaturan Excel: jika rumus dengan koma tidak diterima, gunakan titik koma sebagai pemisah argumen, misalnya=VLOOKUP(E2;$A$2:$C$4;3;FALSE). Fungsi VALUE dan ISNUMBER tersedia pada banyak versi Excel; tampilan nama fungsi bisa berbeda menurut bahasa aplikasi.
Empat pemeriksaan sebelum menyalahkan rumus
1. Kode memang ada di kolom pertama tabel master?
Untuk contoh kita, kode harus berada di kolom A karena range dimulai dari A. Jika kode tidak ada atau berada di kolom lain, pencarian tidak akan menemukan baris yang dimaksud. Pastikan juga nomor kolom hasil tidak melebihi lebar range.
2. Angka dan teks tercampur?
Cek dengan ISNUMBER di kedua tabel. Jika master memakai angka tetapi kode pencarian berupa teks angka, tentukan jenis yang benar sebelum mengubahnya. Jangan otomatis mengonversi kode pelanggan atau kode pos yang mempunyai nol di depan; kode itu biasanya identitas teks dan nol di depannya penting.
3. Ada spasi atau karakter tidak terlihat?
Pada kode teks, AB-02 dan AB-02 (ada spasi di akhir) tidak identik. Coba=TRIM(E2)pada cell bantu jika E2 berisi kode teks, lalu bandingkan dengan master. TRIM menghapus spasi biasa yang berlebih, tetapi tidak otomatis menghapus semua karakter tersembunyi, misalnya non-breaking space dari hasil salin situs web.
4. Apakah pencariannya harus persis?
Untuk kode pegawai, SKU, dan nomor transaksi, gunakanFALSE. Menghilangkan argumen terakhir dapat mengaktifkan pencarian perkiraan dan membuat hasil tidak sesuai, terutama bila master tidak diurutkan seperti yang diperlukan.
Kapan sebaiknya memakai IFNA?
Setelah penyebabnya diperiksa, ada kasus ketika kode memang belum tersedia di master. Untuk tampilan laporan, kamu boleh mengganti pesan #N/A dengan teks yang lebih mudah dibaca:=IFNA(VLOOKUP(E4,$A$2:$C$4,3,FALSE),"Periksa kode"). Contoh tersebut menghasilkan Periksa kode untuk 9999.
IFNA tidak memperbaiki data. Jangan menyamarkan semua error menjadi kosong lalu menganggap rekapan selesai. Untuk data penting, tetap buat daftar kode yang gagal ditemukan agar master bisa diperiksa.
Uji mandiri: satu kode baru, satu hasil baru
Tambahkan baris kelima di tabel master: kode angka 1004, nama Rani, departemen Finance. Ubah range pada rumus menjadi$A$2:$C$5lalu cari angka 1004. Hasil yang benar adalah Finance. Jika masih #N/A, periksa tipe datanya dan pastikan range benar-benar mencakup baris 5.
Ingin latihan dengan masalah Excel lain? Kembali kehub tutorial Excelatau baca cara membuat rekap penjualan Excel. Kalau konsep cell dan range masih membingungkan, mulai daripanduan Excel pemula.