Cara memperbaiki kesalahan #N/A dalam fungsi VLOOKUP

Catatan:  Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. 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 di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Topik ini menjelaskan alasan yang paling umum untuk hasil yang salah pada fungsi VLOOKUP , dan menyediakan saran untuk menggunakan INDEX dan MATCH sebagai gantinya.

Tips: Juga, rujuk ke kartu referensi praktis: tips pemecahan masalah VLOOKUP yang menyajikan alasan umum untuk masalah #NA di file PDF mudah. Anda bisa berbagi PDF dengan orang lain atau mencetak untuk referensi Anda sendiri.

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

Satu batasan Vlookup adalah bahwa hanya bisa mencari nilai di kolom paling kiri dalam array tabel. Jika nilai pencarian Anda tidak berada dalam 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

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

Solusi: Anda bisa Anda coba untuk memperbaiki hal ini dengan menyesuaikan VLOOKUP Anda menjadi referensi kolom yang benar. Jika itu tidak tersedia, lalu coba Pindahkan kolom Anda. Yang juga dapat sangat tidak praktis, jika Anda memiliki lembar bentang besar atau rumit di mana nilai sel adalah hasil dari penghitungan lain — atau mungkin ada alasan lainnya logika mengapa Anda hanya tidak bisa memindahkan kolom sekitar. Solusi adalah menggunakan kombinasi fungsi INDEX dan MATCH, yang bisa mencari nilai dalam kolom terlepas dari posisi lokasi dalam tabel pencarian. Lihat bagian berikutnya.

Pertimbangkan menggunakan indeks MATCH sebagai gantinya

INDEX dan MATCH adalah pilihan yang baik untuk banyak kasus di mana VLOOKUP tidak memenuhi kebutuhan Anda. Keuntungan utama indeks MATCH adalah bahwa Anda bisa mencari nilai dalam kolom di lokasi mana saja dalam tabel pencarian. Indeks mengembalikan nilai dari tabel/rentang yang ditentukan — berdasarkan posisinya. MATCH mengembalikan posisi relatif dari nilai dalam tabel/rentang. Menggunakan INDEX dan MATCH bersama-sama dalam rumus untuk mencari nilai dalam array/tabel dengan menentukan posisi relatif dari nilai dalam tabel/array.

Ada beberapa keuntungan menggunakan indeks MATCH dan bukan VLOOKUP:

  • Dengan INDEX dan MATCH, nilai yang dikembalikan tidak perlu di kolom yang sama seperti kolom pencarian. Ini berbeda dari VLOOKUP, di mana nilai yang dikembalikan harus berada dalam rentang tertentu. Bagaimana hal ini? Dengan VLOOKUP, Anda harus mengetahui jumlah kolom yang berisi nilai yang dikembalikan. Saat ini mungkin tampaknya tidak menantang, bisa rumit saat Anda memiliki tabel besar dan menghitung jumlah kolom. Juga, jika Anda menambahkan/menghapus kolom dalam tabel Anda, Anda harus menceritakan dan memperbarui argumen col_index_num . Dengan INDEX dan MATCH, menghitung tidak diperlukan saat kolom pencarian berbeda dari kolom yang memiliki nilai yang dikembalikan.

  • Dengan INDEX dan MATCH, Anda bisa menentukan baris atau kolom dalam satu array — atau menentukan keduanya. Ini berarti Anda bisa mencari nilai secara vertikal dan horizontal.

  • INDEX dan MATCH bisa digunakan untuk mencari nilai dalam kolom apa pun. Tidak seperti VLOOKUP — di mana Anda bisa hanya mencari nilai di kolom pertama tabel — INDEX dan MATCH akan berfungsi jika nilai pencarian Anda berada dalam kolom pertama, terakhir, atau di mana saja di antara.

  • INDEX dan MATCH menawarkan fleksibilitas membuat dinamis referensi ke kolom yang berisi nilai yang dikembalikan. Ini berarti bahwa Anda bisa menambahkan kolom ke tabel Anda tanpa melanggar INDEX dan MATCH. Di sisi lain, VLOOKUP Hentian jika Anda perlu menambahkan kolom ke tabel — karena itu membuat referensi statis ke tabel.

  • INDEX dan MATCH menawarkan fleksibilitas dengan kecocokan. INDEX dan MATCH bisa menemukan kecocokan persis, atau nilai yang lebih besar atau lebih rendah daripada nilai pencarian. VLOOKUP hanya akan melihat paling cocok dengan nilai (secara default) atau nilai yang tepat. VLOOKUP juga mengasumsikan secara default bahwa kolom pertama dalam array tabel diurutkan secara alfabet, dan misalnya tabel Anda tidak disiapkan dengan cara tersebut, VLOOKUP akan mengembalikan paling cocok pertama dalam tabel, yang mungkin tidak data yang Anda cari.

Sintaks

Untuk membuat sintaks untuk indeks pertandingan, Anda perlu menggunakan argumen array referensi dari fungsi INDEX dan menumpuk sintaks MATCH itu. Membuat formulir:

= INDEX(array or reference, 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:

= Indeks (mengembalikan nilai dari C2:C10, yang akan cocok (Kale, yang merupakan tempat di 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 persis dalam data Anda — ini akan mengembalikan kesalahan #N/A.

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

Juga, pertimbangkan menggunakan fungsi yang bersih atau memangkas 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 daripada nilai terkecil dalam array — Anda akan melihat kesalahan #N/A. Terlihat benar untuk mencocokkan dalam array dan mengembalikan terdekat mendekati nilai lebih rendah 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 lebih besar dengan nilai yang cocok, pertimbangkan menggunakan indeks MATCH dan bukan VLOOKUP — Lihat bagian di atas dalam artikel ini. Dengan indeks/pertandingan, Anda bisa mencari nilai lebih besar dari, lebih kecil untuk, atau sama dengan nilai pencarian. Untuk informasi selengkapnya tentang menggunakan indeks MATCH dan bukan VLOOKUP, lihat bagian sebelumnya dalam topik ini.

Masalah: Kolom pencarian tidak diurutkan dalam urutan naik

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

Solusi:

  • Mengubah fungsi VLOOKUP untuk mencari kecocokan persis. Untuk melakukan itu, setel argumen range_lookup menjadi FALSE. Tidak ada pengurutan diperlukan untuk FALSE.

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

Masalah: Nilainya adalah angka poin mengambang yang besar

Jika Anda memiliki nilai waktu atau besar desimal angka dalam sel, Excel akan mengembalikan kesalahan #N/A karena mengambang titik presisi. Nomor titik mengambang adalah angka yang mengikuti setelah titik desimal. (Excel menyimpan nilai waktu sebagai angka titik mengambang.) Excel tidak bisa menyimpan angka dengan titik mengambang sangat besar, sehingga fungsi agar berfungsi dengan benar, mengambang arahkan angka harus dibulatkan ke angka desimal 5.

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

Apakah Anda memiliki pertanyaan tentang 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

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.

×