Cara memperbaiki kesalahan #N/A dalam fungsi VLOOKUP

Topik ini menjelaskan penyebab paling umum seperti penyebab kegagalan VLOOKUP.

Tips: Lihat Kartu Referensi Cepat: Tips pemecahan masalah VLOOKUP yang menguraikan penyebab umum masalah #NA dengan VLOOKUP dalam file PDF (Portable Document Format) yang praktis. 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 batasan terbesar VLOOKUP adalah fungsi ini hanya dapat mencari nilai di kolom paling kiri dalam larik tabel. Jika nilai pencarian bukan di kolom pertama larik, kesalahan #N/A akan muncul.

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 terjadi karena nilai pencarian "Kale" berada di kolom kedua (Produksi) pada argumen table_array A2:C10 sehingga Excel mencarinya di kolom A, bukan kolom B.

Solusi: Anda dapat memperbaikinya dengan menyesuaikan VLOOKUP untuk mereferensikan kolom yang benar. Jika tidak dapat dilakukan, coba atur dan pindahkan kolom-kolom tersebut. Hal itu dapat merepotkan jika Anda memiliki lembar bentang yang besar atau kompleks dengan nilai sel yang merupakan hasil perhitungan lainnya, atau mungkin terdapat alasan logis lain yang menyebabkan kolom tersebut tidak dapat diatur dan dipindahkan. Sebagai solusi, Anda dapat menggunakan kombinasi fungsi INDEX dan MATCH yang dapat mencari nilai di kolom mana pun dalam tabel pencarian.

Menggunakan INDEX/MATCH dan bukan VLOOKUP

INDEX/MATCH dapat digunakan ketika VLOOKUP tidak dapat memenuhi keinginan Anda. Keuntungan terbesar INDEX/MATCH adalah Anda dapat mencari nilai di kolom mana pun dalam tabel pencarian. INDEX mengembalikan nilai dari tabel/rentang tertentu berdasarkan posisi. MATCH akan mengembalikan posisi relatif nilai dalam tabel/rentang. Menggunakan INDEX dan MATCH secara bersamaan pada rumus, Anda dapat mencari nilai di tabel/larik dengan menentukan posisi relatif nilai pada tabel/larik.

Ada beberapa manfaat menggunakan INDEX/MATCH dibandingkan VLOOKUP:

  • Dengan INDEX dan MATCH, nilai yang dikembalikan tidak perlu berada di kolom yang sama dengan kolom pencarian. Namun, dengan VLOOKUP, nilai yang dikembalikan harus berada di rentang yang ditentukan. Apakah hal tersebut penting? Dengan VLOOKUP, Anda harus mengetahui nomor kolom yang berisi nilai yang dikembalikan. Walaupun sepertinya tidak merepotkan, hal itu akan menyusahkan jika Anda memiliki tabel yang besar dan harus menghitung jumlah kolom. Selain itu, jika ingin menambahkan/menghapus kolom di tabel, Anda harus menghitung ulang dan memperbarui argumen col_index_num. Dengan INDEX dan MATCH, Anda tidak perlu menghitung karena kolom pencarian berbeda dari kolom yang memiliki nilai yang dikembalikan.

  • Dengan INDEX dan MATCH, Anda dapat menentukan baris atau kolom dalam larik atau bahkan menentukan keduanya. Artinya, Anda dapat mencari nilai secara vertikal dan horizontal.

  • INDEX dan MATCH dapat digunakan untuk mencari nilai di kolom mana pun. Berbeda dengan VLOOKUP yang hanya dapat mencari nilai di kolom pertama dalam tabel, INDEX dan MATCH dapat digunakan di kolom pertama, kolom terakhir, atau di mana saja nilai pencarian berada.

  • INDEX dan MATCH menawarkan fleksibilitas dalam membuat referensi dinamis ke kolom yang memiliki nilai yang dikembalikan. Artinya, Anda dapat menambahkan kolom ke tabel dan masih dapat menggunakan INDEX dan MATCH. Di sisi lain, VLOOKUP tidak akan dapat digunakan jika Anda perlu menambahkan kolom ke tabel karena fungsi ini membuat referensi statis pada tabel.

  • INDEX dan MATCH menawarkan lebih banyak fleksibilitas dalam hal pencocokan. INDEX dan MATCH dapat menemukan nilai yang persis sama, lebih besar, atau lebih kecil daripada nilai pencarian. VLOOKUP hanya akan mencari kecocokan terdekat dari sebuah nilai (secara default) atau nilai yang tepat. VLOOKUP juga akan menganggap secara default bahwa kolom pertama dalam larik tabel diurutkan secara alfabet. Jika tabel tidak diatur dengan cara itu, VLOOKUP akan mengembalikan kecocokan terdekat pertama pada tabel, yang mungkin bukan data yang Anda cari.

Sintaks

Guna membuat sintaks untuk INDEX/MATCH, Anda harus menggunakan argumen larik/referensi dari fungsi INDEX dan menumpuk sintaks MATCH ke dalamnya. Penulisannya akan menjadi seperti ini:

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

Mari gunakan INDEX/MATCH untuk mengganti VLOOKUP dengan contoh di atas. Sintaks akan terlihat seperti ini:

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

Dalam bahasa sederhana, hal itu dapat diartikan:

=INDEX(Saya ingin nilai yang dikembalikan dari C2:C10, yang cocok dengan/MATCH(Kale, yang berada di sekitar larik B2:B10, dan nilai yang dikembalikan merupakan 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

Jika argumen range_lookup FALSE dan VLOOKUP tidak dapat menemukan kecocokan yang persis di data Anda, kesalahan #N/A akan muncul.

Solusi: Jika Anda yakin bahwa data yang relevan memang ada di lembar bentang namun VLOOKUP tidak dapat menemukannya, pastikan bahwa sel yang direferensikan tidak memiliki spasi tersembunyi atau karakter non-cetak. Pastikan juga bahwa sel memiliki jenis data yang benar. Sebagai contoh, sel dengan angka harus diformat sebagai Angka, bukan Teks.

Gunakan fungsi CLEAN atau TRIM untuk membersihkan data dalam sel.

Masalah: Nilai pencarian lebih kecil daripada nilai terkecil dalam larik

Jika argumen range_lookup diatur menjadi TRUE dan nilai pencarian lebih kecil daripada nilai terkecil dalam larik, kesalahan #N/A akan muncul. 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 dapat mengubah nilai pencarian dan perlu fleksibilitas yang lebih besar dengan nilai yang cocok, pertimbangkan untuk menggunakan INDEX/MATCH, bukan VLOOKUP. 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 menjadi TRUE dan salah satu kolom pencarian tidak diurutkan dalam urutan naik (A-Z), kesalahan #N/A akan muncul.

Solusi:

  • Ubah fungsi VLOOKUP untuk mencari hasil yang sama persis. Untuk melakukannya, atur argumen range_lookup menjadi FALSE. Anda tidak perlu mengurutkan jika menggunakan 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. (Perhatikan bahwa Excel menyimpan nilai waktu sebagai angka poin mengambang.) Excel tidak dapat menyimpan angka dengan poin mengambang yang besar. Agar fungsi dapat dijalankan dengan benar, angka poin mengambang perlu dibulatkan menjadi 5 angka di sebelah kanan titik 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

Mengoreksi kesalahan #N/A

VLOOKUP: Tidak ada lagi #NA

HLOOKUP, VLOOKUP, mengembalikan nilai yang salah di Excel

Aritmetika poin mengambang dapat memberikan hasil yang tidak akurat di Excel

Kartu Referensi Cepat: Refresh VLOOKUP

Fungsi VLOOKUP

Gambaran umum rumus dalam Excel

Cara menghindari rumus yang tidak berfungsi

Menggunakan pemeriksaan kesalahan untuk mendeteksi kesalahan dalam rumus

Semua fungsi Excel (sesuai alfabet)

Semua fungsi Excel (berdasarkan kategori)

Kembangkan keterampilan 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.

×