 |
| Contoh error #N/A ketika Excel tidak menemukan nilai yang dicari. Sumber gambar: Microsoft Support. |
Pesan #N/A di Excel biasanya berarti rumus pencarian tidak menemukan nilai yang diminta. Pada VLOOKUP, masalah ini dapat muncul karena kode memang tidak ada di tabel, angka tersimpan sebagai teks, ada spasi tersembunyi, atau pengaturan pencarian tidak sesuai. Sebelum mengganti pesan error, cari dulu penyebabnya agar hasil pencarian tidak keliru.
Panduan ini memakai contoh tabel sederhana dan langkah pengecekan berurutan. Anda akan mengetahui cara membaca sintaks VLOOKUP, memperbaiki kecocokan data, memakai pencarian tepat, dan menampilkan pesan yang lebih ramah setelah sumber masalah dipastikan.
Arti error #N/A pada rumus VLOOKUP
#N/A adalah singkatan dari not available. Dalam rumus pencarian, pesan ini menandakan Excel tidak menemukan nilai yang cocok di kolom pencarian. Contohnya, rumus mencari kode barang “BRG-104”, sedangkan tabel sumber hanya berisi “BRG-101” sampai “BRG-103”. Error tersebut bukan selalu kerusakan file; bisa jadi data memang belum tersedia.
Contoh sintaks umum:
=VLOOKUP(D2;$A$2:$B$5;2;FALSE)
Rumus itu mencari isi sel D2 pada kolom pertama dari rentang A2:B5, lalu mengembalikan nilai dari kolom kedua dengan kecocokan tepat. Pada Excel yang memakai koma sebagai pemisah argumen, tulis =VLOOKUP(D2,$A$2:$B$5,2,FALSE). Pemisah yang dipakai mengikuti pengaturan regional perangkat.
1. Pastikan nilai yang dicari benar-benar ada
Mulailah dengan memeriksa nilai pencarian dan tabel sumber. Salin kode dari sel pencarian, lalu gunakan Ctrl+F pada kolom pertama tabel untuk memastikan baris tersebut tersedia. Perhatikan tanda baca, angka nol di depan kode, spasi, dan perbedaan huruf atau format tanggal.
Misalnya kode “00125” berbeda dari angka 125 jika nol di depannya merupakan bagian dari kode. Jangan mengubah kode menjadi angka bila nol tersebut bermakna. Atur kolom sebagai teks sebelum memasukkan kode, atau pertahankan format yang sama di kedua sisi pencarian.
Jika nilai memang belum ada, tambahkan data yang benar ke tabel sumber atau tentukan tindakan untuk data yang belum tercatat. Jangan mengganti #N/A dengan angka nol secara otomatis jika nol dapat disalahartikan sebagai harga, saldo, atau jumlah yang valid.
2. Samakan tipe data angka dan teks
Angka 125 yang tersimpan sebagai angka tidak selalu cocok dengan “125” yang tersimpan sebagai teks. Periksa tanda peringatan pada sel, perataan isi, atau format sel untuk melihat apakah data dibaca sebagai teks. Cara aman adalah menormalkan kolom pencarian dan nilai yang dicari dengan tipe yang sama.
Jika nilai teks memang seharusnya menjadi angka, pilih kolom lalu gunakan Data > Teks ke Kolom > Selesai untuk meminta Excel mengonversi nilai. Alternatifnya, gunakan fungsi VALUE pada kolom bantu. Jangan mengonversi kode identitas yang memiliki nol di depan atau huruf, karena itu dapat mengubah kunci pencarian.
3. Hapus spasi tambahan dan karakter tersembunyi
Spasi di awal atau akhir teks sering tidak terlihat, tetapi membuat dua nilai yang tampak sama menjadi berbeda. Bandingkan panjang teks dengan fungsi LEN, lalu bersihkan data pada kolom bantu menggunakan TRIM. Untuk teks yang berasal dari halaman web atau sistem lain, karakter spasi nonstandar mungkin masih tersisa setelah TRIM.
Untuk membersihkan spasi nonbreaking yang umum tersalin dari web, Anda dapat mencoba formula berikut pada kolom bantu:
=TRIM(SUBSTITUTE(A2;CHAR(160);" "))
Ganti A2 dengan sel yang ingin dibersihkan, lalu salin formula ke bawah. Setelah itu jalankan pencarian terhadap hasil bersih. Bila data mengandung karakter kontrol lain, CLEAN dapat membantu menghapus sebagian karakter tersebut. Simpan kolom asli sampai Anda memastikan hasil normalisasi benar.
4. Periksa kolom pencarian dan nomor indeks
VLOOKUP hanya mencari nilai pada kolom paling kiri dari rentang yang dipilih. Jika kode ada di kolom C tetapi rentang rumus dimulai dari kolom A, Excel tidak akan mencari ke kiri untuk menemukannya. Ubah rentang agar kolom kode menjadi kolom pertama, atau gunakan XLOOKUP jika tersedia dan lebih sesuai dengan susunan tabel.
Argumen ketiga pada VLOOKUP adalah nomor kolom hasil dihitung dari awal rentang, bukan nomor kolom di lembar kerja. Pada rentang A2:C100, angka 2 mengambil nilai dari kolom B. Nomor indeks yang melewati jumlah kolom dapat menimbulkan error berbeda, sedangkan memilih indeks yang keliru bisa menghasilkan jawaban salah tanpa menampilkan #N/A.
5. Gunakan pencarian tepat untuk kode dan nama
Argumen terakhir mengatur jenis kecocokan. Untuk kode produk, nomor pegawai, nama, atau kunci lain yang harus sama persis, gunakan FALSE atau angka 0. Contoh:
=VLOOKUP(D2;$A$2:$B$5;2;FALSE)
Jika argumen terakhir dihilangkan atau diisi TRUE, Excel memakai pencocokan perkiraan yang mengharuskan tabel diurutkan dengan benar. Hasilnya dapat berupa baris yang tidak tepat atau #N/A. Pencocokan perkiraan cocok untuk rentang nilai bertingkat tertentu, bukan untuk pencarian kode yang harus identik.
6. Atur referensi agar tidak bergeser saat rumus disalin
Saat rumus disalin ke baris lain, rentang tabel dapat ikut bergeser. Gunakan tanda dolar untuk mengunci rentang, misalnya $A$2:$B$100. Anda juga dapat mengubah data sumber menjadi Tabel Excel sehingga rentang lebih mudah diperluas saat baris baru ditambahkan.
Setelah mengunci rentang, periksa kembali sel pencarian pada setiap baris. Pastikan setiap rumus merujuk ke baris yang benar, terutama setelah menyalin rumus melintasi kolom atau mengurutkan data.
Gunakan IFNA setelah penyebabnya diketahui
Setelah data dan rumus benar, Anda dapat mengganti pesan #N/A dengan keterangan yang mudah dipahami. Contoh:
=IFNA(VLOOKUP(D2;$A$2:$B$5;2;FALSE);"Data tidak ditemukan")
IFNA hanya menangani error #N/A. IFERROR menangani berbagai jenis error lain juga, sehingga dapat menyembunyikan masalah rumus yang seharusnya diperiksa. Gunakan pesan kosong seperti "" hanya bila memang jelas bahwa data yang belum ditemukan tidak perlu ditampilkan kepada pembaca.
Untuk memeriksa hasil, uji formula dengan satu nilai yang pasti ada dan satu nilai yang memang tidak ada. Pastikan nilai pertama menghasilkan informasi dari baris yang benar dan nilai kedua menunjukkan pesan yang Anda inginkan.
Alternatif pencarian dengan XLOOKUP
Jika versi Excel Anda mendukung XLOOKUP, fungsi ini dapat mencari ke kiri atau ke kanan dan memiliki argumen khusus untuk nilai yang tidak ditemukan. Pola dasarnya seperti =XLOOKUP(D2;A2:A100;B2:B100;"Data tidak ditemukan"). Pastikan rentang nilai pencarian dan rentang hasil memiliki ukuran yang sama. Untuk file yang akan dibuka di Excel versi lama, periksa kompatibilitas sebelum mengganti seluruh VLOOKUP.
Checklist cepat memperbaiki #N/A
- Pastikan nilai yang dicari tersedia dalam kolom pertama rentang.
- Samakan tipe data: angka dengan angka, teks dengan teks.
- Periksa spasi awal, spasi akhir, karakter nonstandar, dan nol di depan.
- Gunakan FALSE atau 0 untuk pencarian tepat atas kode dan nama.
- Pastikan rentang dan nomor indeks kolom menunjuk ke data yang benar.
- Kunci rentang dengan tanda dolar sebelum menyalin formula.
- Gunakan IFNA hanya setelah penyebab error dipahami.
Untuk tutorial kerja digital lainnya, jelajahi kumpulan panduan Microsoft Office di PramuTekno. Panduan di halaman label itu membantu Anda menemukan langkah untuk Word, Excel, dan aplikasi Office lainnya.
Kesimpulan
Error #N/A pada VLOOKUP paling sering terjadi karena nilai tidak ditemukan atau bentuk datanya berbeda. Periksa sumber nilai, tipe data, spasi, posisi kolom pencarian, mode kecocokan, dan rentang rumus satu per satu. Setelah hasil pencarian akurat, gunakan IFNA untuk menampilkan pesan yang lebih jelas tanpa menutupi masalah lain.
0Komentar