Sebab Mengapa Rumus VLOOKUP Tidak Berfungsi di Excel

Fungsi VLOOKUP di Excel berguna untuk mencari data dalam tabel. Namun, tidak jarang pengguna menghadapi masalah di mana rumus VLOOKUP tidak berfungsi sebagaimana mestinya.

Artikel ini membahas berbagai penyebab umum dan solusi efektif untuk mengatasi masalah tersebut.

File: rumus-vlookup-tidak-berfungsi.xlsx.

Mengenali Masalah pada Rumus VLOOKUP

Secara umum, ada dua bentuk kegagalan dalam rumus VLOOKUP:

1. Pesan error seperti #N/A, #REF!, #VALUE!, atau #NAME!.
2. Hasil tidak sesuai harapan, di mana data yang dikembalikan salah atau tidak akurat.

Kolom Pencarian Bukan Kolom Terkiri

VLOOKUP selalu mencari lookup_value di kolom pertama dalam table_array. Jika kolom pencarian berada di tengah atau kanan, VLOOKUP tidak akan bekerja sebagaimana mestinya.

Solusi:

• Pastikan kolom pertama dari table_array adalah kolom di mana lookup_value dicari.
• Gunakan INDEX dan MATCH atau XLOOKUP jika perlu mengambil data di kiri kolom pencarian.

  =INDEX(A2:A6; MATCH(A9; B2:B6; 0))

  =XLOOKUP(A9; B2:B6; A2:A6)

VLOOKUP Tidak Bisa Mengambil Data di Kiri

Kondisi ini masih berhubungan dengan bagian sebelumnya.

VLOOKUP hanya bisa mengambil data dari kolom sebelah kanan kolom pencarian dan tidak bisa mengambil data di kiri kolom pencarian.

Solusi:

• Gunakan kombinasi INDEX dan MATCH atau XLOOKUP untuk mengambil data dari kolom kiri.

Posisi col_index_num Tidak Sesuai

col_index_num menentukan kolom ke berapa dalam table_array hasil rumus akan diambil. Jika nilainya melebihi jumlah kolom yang tersedia atau tidak menunjukkan posisi kolom yang diinginkan, VLOOKUP akan mengembalikan #REF! atau hasil tidak sesuai.

Solusi:

• Pastikan col_index_num sesuai dengan struktur tabel atau kolom yang diinginkan.
• Gunakan fungsi MATCH untuk menentukan nomor kolom secara dinamis.

  =VLOOKUP(A9; A2:C6; MATCH(B8; A1:C1))

Kesalahan Referensi Relatif dan Absolut

Saat rumus VLOOKUP disalin ke cell lain, referensi table_array dapat berubah secara otomatis (relatif) sehingga terjadi error atau hasil tidak sesuai.

Solusi:

• Gunakan referensi absolut untuk mengunci table_array agar tidak berubah.

  =VLOOKUP(D2; $A$2:$B$6; 2; FALSE)

• Gunakan nama range untuk referensi lebih fleksibel.

  =VLOOKUP(D2; DataPenjualan; 2; FALSE)

Penambahan Kolom Baru

Jika sebuah kolom baru ditambahkan di depan kolom nilai kembalian, hasil VLOOKUP bisa menjadi tidak valid karena col_index_num tidak berubah.

Solusi:

• Hindari penggunaan hardcode bagi col_index_num dan gunakan MATCH (atau cara lainnya) untuk menemukan posisi kolom secara dinamis.

  =VLOOKUP(E2; A2:C6; MATCH("Penjualan"; A1:C1; 0); FALSE)

• Periksa ulang rumus setiap kali ada perubahan struktur tabel.
• Gunakan nama range atau tabel data.

Keliru Penggunaan Approximate vs Exact Match

Parameter range_lookup memiliki dua opsi:

• TRUE (default): Mencari nilai sama atau nilai terbesar yang lebih kecil dari lookup_value dan data pada kolom pencarian table_array harus berurutan. Jika tidak diurutkan, hasil bisa salah.
• FALSE: Mencari nilai yang persis sama dengan lookup_value.

Solusi:

• Gunakan FALSE jika perlu pencarian persis.

• Pastikan kolom pencarian berurutan jika menggunakan TRUE.

Penambahan Data Tidak Terakomodasi

Jika tabel diperbarui dengan data baru tetapi table_array tidak diperluas, VLOOKUP tidak dapat menemukan data tersebut.

Solusi:

• Periksa ulang cakupan tabel setelah menambahkan data baru.
• Gunakan data dengan format tabel agar data baru otomatis diakomodasi.

Duplikasi Data di Kolom Pencarian

Jika ada beberapa nilai yang sama di kolom pencarian, VLOOKUP hanya akan mengembalikan nilai pertama yang ditemukan.

Solusi:

• Gunakan data unik sebagai kunci pencarian (misalnya kode ID, bukan nama).
• Jika perlu mencari semua hasil, gunakan fungsi FILTER (untuk Excel 365 atau 2021 ke atas).

  =FILTER(B2:B7; A2:A7=D2)

Perbedaan Tipe Data antara lookup_value dan Kolom Pencarian

Jika lookup_value berbeda tipe data, bisa jadi rumus VLOOKUP akan gagal. Pada gambar, lookup_value adalah teks sementara data kolom pencarian adalah tanggal.

Solusi:

• Pastikan tipe data sama dengan ISTEXT dan ISNUMBER.

• Konversi data menggunakan VALUE atau TEXT.

  =VLOOKUP(VALUE(E2); A2:B6; 2)*1%

lookup_value Melebihi 255 Karakter

Jika lookup_value lebih panjang dari 255 karakter, VLOOKUP akan mengembalikan #VALUE!.

Solusi:

• Gunakan kunci pencarian lebih singkat, jika memungkinkan.

Karakter Tidak Terlihat dalam lookup_value

Kadang teks mengandung spasi atau karakter tak terlihat yang menyebabkan pencarian gagal.

Salah satu cara mengetahuinya adalah dengan membandingkan lookup_value dengan data pada table_array. Contoh: =E2=A6. Bila hasil FALSE, berarti kedua cell berbeda nilai.

Solusi:

• Hapus karakter tidak terlihat baik pada lookup_value atau kolom pencarian table_array.
• Gunakan fungsi TRIM untuk menghapus spasi berlebih.

  =VLOOKUP(TRIM(E2); A2:B6; 2; FALSE)

VLOOKUP Beda Sheet atau File Tidak Berfungsi

VLOOKUP bisa gagal jika referensi ke sheet lain atau file lain tidak benar.

Solusi:

• Pastikan format referensi sudah benar.
• Jika mencari di file lain, pastikan file sumber terbuka atau tulis path lengkap.

Nama Range atau Cakupannya Tidak Tepat

Salah menggunakan nama range atau nama range tidak mencakup data yang tepat dapat berakibat hasil tidak sesuai atau pesan error #NAME!.

Solusi:

• Pastikan nama range sudah tepat.
• Pastikan nama range mencakup data yang kita inginkan.
• Gunakan IntelliSense saat menuliskan nama range.
• Gunakan tabel data.

Menangani Error VLOOKUP

Pada akhirnya, error seperti #N/A, #REF!, #NAME?, atau #VALUE! tidak dapat dihindari. Untuk menangani error-error tersebut, gunakan IFNA (bila error berpotensi #N/A) atau IFERROR (untuk #REF!, #NAME?, atau #VALUE!).

=IFNA(VLOOKUP(A9; A2:B6; 1); "")

=IFERROR(VLOOKUP(A9; A2:B6; 1); "Error")

Penutup

Rumus VLOOKUP bisa gagal karena berbagai alasan, tetapi dengan memahami penyebab umum dan menerapkan solusi yang tepat, kita dapat meningkatkan akurasi data di Excel.