Mengapa VLOOKUP Terkadang Gagal

VLOOKUP dapat diandalkan, tetapi dapat gagal dengan beberapa cara yang umum dan mudah diperkirakan. Sebagian besar kesalahan tidaklah misterius setelah Anda memahami aturannya.

Dalam pelajaran ini, Anda akan mempelajari penyebab umum pencarian yang gagal dan cara tepat untuk memperbaiki masing-masing penyebab. Dengan memahaminya, Anda dapat mengubah error #N/A dan #REF! yang membingungkan menjadi perbaikan cepat dan mudah.

VLOOKUP hanya dapat mencari di kolom paling kiri dari susunan_tabel dan mengembalikan nilai ke arah kanan. Fungsi ini tidak dapat mencari suatu nilai lalu mengembalikan hasil yang berada di sebelah kirinya.

Jika ID Anda berada di kolom C dan nama yang Anda inginkan berada di kolom A, VLOOKUP tidak dapat mencarinya ke arah kiri. Pilihan Anda adalah menyusun ulang kolom agar kolom pencarian menjadi yang pertama, atau menggunakan INDEX-MATCH atau XLOOKUP, yang dapat mencari ke arah mana pun.

nomor_indeks_kolom dihitung dari sisi kiri susunan_tabel, bukan dari lembar kerja. Kesalahan yang umum terjadi adalah menggunakan huruf kolom lembar kerja sebagai angkanya.

Jika rentang Anda adalah C1:F10 dan Anda menginginkan kolom F, kolom tersebut adalah kolom ke-4 dalam rentang, sehingga indeksnya adalah 4, bukan 6. Menghitung dari sisi yang salah akan mengembalikan bidang yang keliru atau, jika angkanya melebihi lebar rentang, error #REF!.

Jika nomor_indeks_kolom lebih besar daripada jumlah kolom dalam susunan_tabel, VLOOKUP mengembalikan #REF!.

Misalnya, meminta kolom 5 dari rentang yang terdiri atas 3 kolom, yaitu A1:C10, tidaklah mungkin:

Perbaiki masalah ini dengan memperlebar susunan_tabel agar mencakup kolom yang Anda perlukan, atau dengan mengoreksi indeks menjadi nomor kolom yang benar-benar ada dalam rentang.

Menghilangkan argumen keempat akan menetapkan nilai bawaan TRUE (perkiraan). Pada daftar yang tidak diurutkan, hal ini diam-diam mengembalikan nilai di sebelahnya yang salah, bukan error, sehingga sulit diketahui.

Perbaikannya sederhana dan sebaiknya menjadi kebiasaan: selalu tambahkan FALSE untuk pencarian tepat.

Nilai pencarian "A100" tidak akan cocok dengan "A100 " yang memiliki spasi di belakang. Data yang diimpor penuh dengan perbedaan tak terlihat seperti ini.

Gejalanya: suatu nilai jelas ada, tetapi Anda mendapatkan #N/A. Bersihkan kedua sisi dengan TRIM untuk menghapus spasi yang tidak diperlukan:

Jika nilai pencarian Anda adalah angka 100, tetapi tabel menyimpan kode sebagai teks "100" (atau sebaliknya), keduanya tidak akan cocok dan Anda mendapatkan #N/A.

Perhatikan segitiga hijau kecil atau angka yang rata kiri sebagai tanda bahwa angka tersebut berupa teks. Perbaiki dengan mengonversinya: bungkus teks dengan VALUE() agar menjadi angka, atau gabungkan string kosong dengan angka menggunakan &"" agar menjadi teks, sehingga kedua sisi memiliki tipe yang sama.

Jika Anda lupa mengunci susunan_tabel, menyalin rumus ke bawah akan menyeret rentang menjauh dari data Anda. A1:C100 pada baris 2 menjadi A2:C101 pada baris 3, lalu A3:C102, sehingga beberapa baris terlewat.

Perbaiki dengan referensi absolut agar tabel tetap tetap sementara hanya nilai pencarian yang bergeser:

Setiap error menunjukkan penyebab tertentu:

• #N/A - nilai tidak ditemukan (ketidakcocokan, spasi, tipe yang salah, atau memang tidak ada)
• #REF! - nomor_indeks_kolom lebih besar daripada rentang, atau sel yang dirujuk telah dihapus
• #VALUE! - suatu argumen memiliki tipe yang salah, seperti indeks kolom negatif atau nol
• #NAME? - nama fungsi salah eja, seperti VLOOKP

Jika Anda mencocokkan error dengan maknanya, berarti separuh masalah sudah teratasi.

Saat melakukan debug, Anda juga dapat membungkus pencarian agar pengguna melihat pesan yang jelas, bukan error mentah. IFERROR menangkap error apa pun dan mengembalikan teks yang Anda tentukan sebagai gantinya.

Ini tidak memperbaiki penyebab yang mendasarinya, jadi gunakan hanya setelah Anda memahami alasan pencarian gagal. Menyembunyikan error terlalu dini dapat menutupi masalah data yang sebenarnya.

Saat pencarian tidak berjalan semestinya, periksa daftar singkat berikut:

• Apakah nilai pencarian berada di kolom pertama rentang?
• Apakah nomor_indeks_kolom dihitung dari sisi kiri rentang dan berada dalam lebar rentang?
• Apakah Anda menambahkan FALSE untuk pencarian tepat?
• Apakah kedua sisi memiliki tipe yang sama (teks atau angka) dan tidak memiliki spasi yang tidak diperlukan?
• Apakah susunan_tabel dikunci dengan tanda dolar?

Memeriksa daftar ini dari atas ke bawah menyelesaikan sebagian besar kegagalan pencarian dalam hitungan detik.

Penyebab umum dan perbaikannya:

• Batas kolom paling kiri - susun ulang kolom, atau gunakan INDEX-MATCH / XLOOKUP
• nomor_indeks_kolom yang salah atau terlalu besar - hitung dari sisi kiri rentang; lebarkan rentang
• FALSE tidak dicantumkan - selalu tetapkan pencarian tepat untuk ID
• Spasi dan teks versus angka - bersihkan dengan TRIM, konversikan dengan VALUE atau &""
• Tabel tidak dikunci - gunakan $ agar rentang tetap pada tempatnya

Baca kode error, cocokkan dengan penyebabnya, lalu terapkan perbaikannya. Sekarang Anda memiliki perangkat lengkap untuk melakukan pencarian yang andal.