Cara memperbaiki kesalahan #N/A dalam fungsi VLOOKUP

Topik ini menguraikan alasan Vlookup yang paling umum untuk hasil yang keliru pada fungsi tersebut, dan memberikan saran untuk menggunakan indeks dan kecocokan .

Tips: Lihat juga pada kartu referensi cepat: Tips pemecahan masalah Vlookup yang menyajikan alasan umum untuk masalah #NA dalam file PDF yang nyaman. Anda dapat berbagi PDF dengan orang lain atau mencetaknya untuk referensi diri sendiri.

Masalah: Nilai pencarian tidak berada dalam kolom pertama di argumen table_array

Salah satu kendala VLOOKUP adalah hanya bisa mencari nilai di kolom paling kiri dalam array tabel. Jika nilai pencarian Anda tidak berada di kolom pertama dari array, Anda akan melihat kesalahan #N/A.

Di tabel berikut, kami ingin mengambil jumlah unit yang terjual untuk Kale.

Kesalahan #NA dalam VLOOKUP: Nilai pencarian tidak berada dalam kolom pertama larik tabel

Hasil kesalahan #N/A karena nilai pencarian "Kale" muncul di kolom kedua (hasil) argumen Table_array A2: C10. Dalam kasus ini, Excel mencarinya di kolom A, bukan kolom B.

Solusi: Anda dapat memperbaikinya dengan menyesuaikan VLOOKUP untuk mereferensikan kolom yang benar. Jika tidak memungkinkan, coba Pindahkan kolom Anda. Yang mungkin juga sangat tidak praktis, jika Anda memiliki lembar bentang besar atau kompleks di mana nilai sel adalah hasil perhitungan lain — atau mungkin ada beberapa alasan logis lainnya mengapa Anda tidak bisa memindahkan kolom di sekitar. Sebagai solusi, Anda dapat menggunakan kombinasi fungsi INDEX dan MATCH yang dapat mencari nilai di kolom mana pun dalam tabel pencarian. Lihat bagian berikutnya.

Pertimbangkan menggunakan INDEX/MATCH sebagai gantinya

Indeks dan kecocokan adalah opsi yang baik untuk banyak kasus di mana Vlookup tidak memenuhi kebutuhan Anda. Keuntungan utama dari INDEX/MATCH adalah Anda bisa mencari nilai dalam kolom di lokasi mana pun dalam tabel pencarian. Indeks mengembalikan nilai dari tabel/rentang yang ditentukan — sesuai dengan posisinya. KECOCOKAN mengembalikan posisi relatif dari nilai dalam tabel/rentang. Menggunakan INDEX dan MATCH bersama-sama dalam sebuah rumus untuk mencari nilai dalam tabel/array dengan menentukan posisi relatif dari nilai dalam tabel/array.

Ada beberapa manfaat menggunakan INDEX/MATCH sebagai ganti VLOOKUP:

  • Dengan indeks dan kecocokan, nilai yang dikembalikan tidak perlu berada di kolom yang sama dengan kolom pencarian. Ini berbeda dari VLOOKUP, di mana nilai yang dikembalikan harus berada dalam rentang yang ditentukan. Bagaimana hal ini? Dengan VLOOKUP, Anda harus mengetahui nomor kolom yang berisi nilai yang dikembalikan. Meskipun ini mungkin tidak terlalu menantang, hal ini bisa merepotkan saat Anda memiliki tabel besar dan harus menghitung jumlah kolom. Selain itu, jika Anda menambahkan/menghapus kolom dalam tabel, Anda harus menghitung ulang dan memperbarui argumen col_index_num . Dengan indeks dan kecocokan, tidak ada penghitungan yang diperlukan karena kolom pencarian berbeda dari kolom yang memiliki nilai yang dikembalikan.

  • Dengan indeks dan kecocokan, Anda bisa menentukan baris atau kolom dalam array — atau menentukan keduanya. Artinya, Anda dapat mencari nilai secara vertikal dan horizontal.

  • INDEX dan MATCH dapat digunakan untuk mencari nilai di kolom mana pun. Tidak seperti VLOOKUP — di mana Anda hanya bisa mencari nilai di kolom pertama dalam tabel — indeks dan kecocokan akan berfungsi jika nilai pencarian Anda berada di kolom pertama, yang terakhir, atau di antaranya.

  • Indeks dan kecocokan menawarkan fleksibilitas dalam membuat referensi dinamis ke kolom yang berisi nilai yang dikembalikan.Ini berarti Anda bisa menambahkan kolom ke tabel tanpa melanggar indeks dan kecocokan. Di sisi lain, VLOOKUP keluar jika Anda perlu menambahkan kolom ke tabel-karena membuat referensi statis ke tabel.

  • Indeks dan kecocokan menawarkan lebih banyak fleksibilitas dengan kecocokan.Indeks dan kecocokan dapat menemukan kecocokan persis, atau nilai yang lebih besar atau lebih kecil dari nilai pencarian. VLOOKUP hanya akan mencari kecocokan terdekat dengan nilai (secara default) atau nilai yang tepat. VLOOKUP juga menganggap secara default bahwa kolom pertama dalam array tabel diurutkan menurut abjad, dan Misalkan tabel Anda tidak diatur sedemikian rupa, VLOOKUP akan mengembalikan kecocokan terdekat pertama dalam tabel, yang mungkin bukan data yang Anda cari.

Sintaks

Untuk membuat sintaks untuk indeks/kecocokan, Anda perlu menggunakan argumen array/Reference dari fungsi INDEX dan menggabungkan sintaks kecocokan di dalamnya. Ini mengambil formulir:

=INDEX(array atau referensi, MATCH(lookup_value,lookup_array,[match_type])

Mari kita gunakan indeks/kecocokan untuk mengganti VLOOKUP dari contoh di atas. Sintaks akan terlihat seperti ini:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

Dalam bahasa sederhana, hal itu dapat diartikan:

= INDEX (mengembalikan nilai dari C2: C10, yang akan cocok (Kale, yang merupakan suatu tempat dalam array B2: B10, di mana nilai yang dikembalikan adalah nilai pertama yang terkait dengan Kale))

Fungsi INDEX dan MATCH dapat digunakan sebagai pengganti VLOOKUP

Rumus akan mencari nilai pertama di C2:C10 yang terkait dengan Kale (di B7) dan mengembalikan nilai di C7 (100), yang merupakan nilai pertama yang cocok dengan Kale.

Masalah: Kecocokan yang persis tidak ditemukan

Saat argumen range_lookup adalah false — dan Vlookup tidak dapat menemukan kecocokan yang sama persis dalam data Anda — ini mengembalikan kesalahan #N/a.

Solusi: jika Anda yakin data yang relevan ada di lembar bentang Anda dan Vlookup tidak mengambilnya, luangkan waktu untuk memverifikasi bahwa sel yang direferensikan tidak memiliki spasi atau karakter non-cetak yang tersembunyi. Selain itu, pastikan sel mengikuti tipe data yang benar. Misalnya, sel dengan angka harus diformat sebagai angka, dan bukan teks.

Selain itu, pertimbangkan untuk menggunakan fungsi Bersihkan atau Pangkas untuk membersihkan data dalam sel.

Masalah: Nilai pencarian lebih kecil daripada nilai terkecil dalam larik

Jika argumen range_lookup diatur ke True — dan nilai pencarian lebih kecil dari nilai terkecil dalam array — Anda akan melihat kesalahan #N/a. TRUE mencari hasil yang mendekati dalam larik dan mengembalikan nilai terdekat yang lebih kecil daripada nilai pencarian.

Pada contoh berikut, nilai pencarian adalah 100, namun tidak ada nilai dalam rentang B2:C10 yang kurang dari 100. Oleh karena itu, kesalahan akan muncul.

Kesalahan N/A di VLOOKUP ketika nilai pencarian lebih kecil daripada nilai terkecil dalam larik

Solusi:

  • Perbaiki nilai pencarian yang diperlukan.

  • Jika Anda tidak bisa mengubah nilai pencarian dan memerlukan fleksibilitas yang lebih besar dengan nilai yang cocok, pertimbangkan menggunakan INDEX/MATCH dan bukan VLOOKUP — Lihat bagian di atas dalam artikel ini. Dengan INDEX/MATCH, Anda dapat mencari nilai yang lebih besar, lebih kecil, atau sama dengan nilai pencarian. Untuk informasi selengkapnya tentang penggunaan INDEX/MATCH, lihat bagian sebelumnya dalam topik ini.

Masalah: Kolom pencarian tidak diurutkan dalam urutan naik

Jika argumen range_lookup diatur ke True — dan salah satu dari kolom pencarian Anda tidak diurutkan dalam urutan naik (a-Z) — Anda akan melihat kesalahan #N/a.

Solusi:

  • Ubah fungsi VLOOKUP untuk mencari kecocokan yang sama persis. Untuk melakukan itu, atur argumen range_lookup menjadi false. Tidak ada pengurutan yang diperlukan untuk FALSE.

  • Gunakan fungsi INDEX/MATCH untuk mencari nilai pada tabel yang tidak diurutkan.

Masalah: Nilainya adalah angka poin mengambang yang besar

Jika sel berisi nilai waktu atau angka desimal yang besar, Excel akan mengembalikan kesalahan #N/A karena ketepatan poin mengambang. Angka poin mengambang merupakan angka yang berada di sebelah kanan titik desimal. (Excel menyimpan nilai waktu sebagai angka Floating Point.) Excel tidak bisa menyimpan angka dengan titik mengambang yang sangat besar, sehingga untuk fungsi tersebut bekerja dengan benar, angka Floating Point harus dibulatkan ke 5 tempat desimal.

Solusi: Perpendek angka dengan membulatkannya ke atas menjadi lima angka di sebelah kanan titik desimal dengan fungsi ROUND.

Punya pertanyaan fungsi tertentu?

Posting pertanyaan dalam forum komunitas Excel

Bantu kami meningkatkan Excel

Apakah Anda memiliki saran agar kami dapat menyempurnakan versi Excel berikutnya? Jika demikian, silakan periksa topik yang ada di Suara Pengguna Excel.

Lihat Juga

Catatan:  Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda? Berikut adalah artikel dalam bahasa Inggris untuk referensi.

Perlu bantuan lainnya?

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×