Cara Memecahkan Masalah Kesalahan VLOOKUP di Excel

Bagaimana-Untuk

OlehBen Stockton

Terakhir diperbarui saat13 Januari 2020

Ada beberapa hal yang mengeluarkan keringat Microsoft Excel pengguna lebih dari memikirkan kesalahan VLOOKUP. Jika Anda tidak terlalu terbiasa dengan Excel, VLOOKUP adalah salah satu fungsi yang paling sulit, atau setidaknya salah satu yang paling tidak dipahami, tersedia.

Tujuan VLOOKUP adalah untuk mencari dan mengembalikan data dari kolom lain di lembar bentang Excel Anda. Sayangnya, jika Anda salah rumus VLOOKUP Anda, Excel akan melemparkan kesalahan pada Anda. Mari kita mengalami beberapa kesalahan VLOOKUP yang umum dan menjelaskan cara mengatasi masalah tersebut.

Keterbatasan VLOOKUP

Sebelum Anda mulai menggunakan VLOOKUP, Anda harus menyadari bahwa itu tidak selalu merupakan pilihan terbaik untuk pengguna Excel.

Untuk memulainya, itu tidak dapat digunakan untuk mencari data di sebelah kiri itu. Itu juga hanya akan menampilkan nilai pertama yang ditemukannya, yang berarti bahwa VLOOKUP bukan pilihan untuk rentang data dengan nilai duplikat. Kolom pencarian Anda juga harus menjadi kolom kiri terjauh dalam rentang data Anda.

Pada contoh di bawah ini, kolom terjauh (kolom A) digunakan sebagai kolom pencarian. Tidak ada nilai duplikat dalam rentang, dan data pencarian (dalam hal ini, data dari kolom B) ada di sebelah kanan kolom pencarian.

Fungsi INDEX dan MATCH akan menjadi alternatif yang baik jika salah satu dari masalah ini adalah masalah, seperti halnya fungsi XLOOKUP baru di Excel, saat ini dalam pengujian beta.

VLOOKUP juga mensyaratkan data disusun dalam baris untuk dapat secara akurat mencari dan mengembalikan data. HLOOKUP akan menjadi alternatif yang baik jika ini bukan masalahnya.

Ada beberapa batasan tambahan untuk formula VLOOKUP yang dapat menyebabkan kesalahan, seperti yang akan kami jelaskan lebih lanjut.

Kesalahan VLOOKUP dan # N / A

Salah satu kesalahan VLOOKUP paling umum di Excel adalah # N / A kesalahan. Kesalahan ini terjadi ketika VLOOKUP tidak dapat menemukan nilai yang Anda cari.

Untuk mulai dengan, nilai pencarian mungkin tidak ada dalam rentang data Anda, atau Anda mungkin menggunakan nilai yang salah. Jika Anda melihat kesalahan N / A, periksa kembali nilainya dalam rumus VLOOKUP Anda.

Jika nilainya benar, maka nilai pencarian Anda tidak ada. Ini mengasumsikan Anda menggunakan VLOOKUP untuk menemukan kecocokan yang tepat, dengan range_lookup argumen diatur ke SALAH.

Dalam contoh di atas, mencari a Identitas Siswa dengan angka 104 (dalam sel G4) mengembalikan sebuah # N / A kesalahan karena nomor ID minimum dalam kisaran adalah 105.

Jika range_lookup argumen di akhir rumus VLOOKUP Anda tidak ada atau disetel ke BENAR, maka VLOOKUP akan mengembalikan kesalahan # N / A jika rentang data Anda tidak diurutkan dalam urutan menaik.

Ini juga akan mengembalikan kesalahan # N / A jika nilai pencarian Anda lebih kecil dari nilai terendah dalam rentang.

Pada contoh di atas, Identitas Siswa nilai digabungkan. Meskipun memiliki nilai 105 yang ada dalam jangkauan, VLOOKUP tidak dapat menjalankan pencarian yang benar dengan range_lookup argumen diatur ke BENAR karena kolom A tidak diurutkan dalam urutan menaik.

Alasan umum lainnya untuk kesalahan # N / A termasuk menggunakan kolom pencarian yang bukan sel terjauh dan menggunakan sel referensi untuk nilai pencarian yang berisi angka tetapi diformat sebagai teks, atau mengandung kelebihan karakter seperti spasi.

Pemecahan Masalah Kesalahan #VALUE

Itu #NILAI kesalahan biasanya merupakan tanda bahwa rumus yang mengandung fungsi VLOOKUP salah dalam beberapa hal.

Dalam kebanyakan kasus, ini biasanya karena sel yang Anda referensikan sebagai nilai pencarian Anda. Itu ukuran maksimum dari nilai pencarian VLOOKUP adalah 255 karakter.

Jika Anda berurusan dengan sel yang berisi string karakter yang lebih panjang, VLOOKUP tidak akan bisa menanganinya.

Satu-satunya solusi untuk ini adalah mengganti formula VLOOKUP Anda dengan formula INDEX dan MATCH gabungan. Misalnya, di mana satu kolom berisi sel dengan string lebih dari 255 karakter, fungsi MATCH bersarang di dalam INDEX dapat digunakan untuk mencari data ini sebagai gantinya.

Dalam contoh di bawah ini, INDEKS mengembalikan nilai dalam sel B4, menggunakan rentang dalam kolom A untuk mengidentifikasi baris yang benar. Ini menggunakan bersarang PERTANDINGAN berfungsi untuk mengidentifikasi string di kolom A (mengandung 300 karakter) yang cocok dengan sel H4.

Dalam hal ini, itu adalah sel A4, dengan INDEKS kembali 108 (nilai dari B4).

Kesalahan ini juga akan muncul jika Anda menggunakan referensi sel yang salah dalam rumus Anda, terutama jika Anda menggunakan rentang data dari buku kerja lain.

Referensi buku kerja harus dilampirkan dalam tanda kurung siku agar rumus berfungsi dengan benar.

Jika Anda menemukan kesalahan #VALUE, periksa ulang rumus VLOOKUP Anda untuk mengonfirmasi bahwa referensi sel Anda benar.

#NAME dan VLOOKUP

Jika kesalahan VLOOKUP Anda bukan kesalahan #VALUE atau kesalahan # N / A, maka kemungkinan itu adalah #NAMA kesalahan. Sebelum Anda panik memikirkan hal ini, yakinlah — ini adalah kesalahan VLOOKUP termudah untuk diperbaiki.

Kesalahan #NAME muncul ketika Anda salah mengeja fungsi di Excel, apakah itu VLOOKUP atau fungsi lain seperti SUM. Klik sel VLOOKUP Anda dan periksa kembali apakah Anda benar-benar mengeja VLOOKUP dengan benar.

Jika tidak ada masalah lain, rumus VLOOKUP Anda akan berfungsi setelah kesalahan ini diperbaiki.

Menggunakan Fungsi Excel Lainnya

Itu pernyataan yang berani, tetapi fungsinya suka VLOOKUP akan mengubah hidup Anda. Paling tidak, itu akan mengubah kehidupan kerja Anda, menjadikan Excel alat yang lebih kuat untuk analisis data.

Jika VLOOKUP tidak cocok untuk Anda, maka manfaatkan ini fungsi Excel teratas sebagai gantinya.