Topik ini menjelaskan alasan VLOOKUP yang paling umum untuk hasil yang salah pada fungsi dan menyediakan saran untuk menggunakan INDEX dan MATCH sebagai gantinya.
Tips: Lihat juga 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
Satu batasan VLOOKUP adalah VLOOKUP hanya dapat mencari nilai di kolom paling kiri dalam array tabel. Jika nilai pencarian tidak berada di kolom pertama array, Anda akan melihat kesalahan #N/A.
Di tabel berikut, kami ingin mengambil jumlah unit yang terjual untuk Kale.
Hasil kesalahan #N/A karena nilai pencarian "Kale" muncul di kolom kedua (Hasil Bumi) argumen table_array A2:C10. Dalam hal 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. Itu mungkin juga sangat tidak bisa dipahami, jika Anda memiliki lembar bentang besar atau kompleks di mana nilai sel merupakan hasil dari penghitungan lain—atau mungkin ada alasan logis lain mengapa Anda tidak bisa memindahkan kolom di sekitarnya. 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 untuk menggunakan INDEX/MATCH sebagai gantinya
INDEX dan MATCH adalah opsi yang bagus untuk banyak kasus di mana VLOOKUP tidak memenuhi kebutuhan Anda. Keuntungan utama INDEX/MATCH adalah Anda dapat mencari nilai dalam kolom di lokasi mana pun dalam tabel pencarian. INDEX mengembalikan nilai dari tabel/rentang tertentu—sesuai dengan posisinya. MATCH mengembalikan posisi relatif nilai dalam tabel/rentang. Gunakan INDEX dan MATCH bersama-sama dalam rumus untuk mencari nilai dalam tabel/array dengan menentukan posisi relatif nilai dalam tabel/array.
Ada beberapa manfaat menggunakan INDEX/MATCH, bukan VLOOKUP:
-
Dengan INDEX dan MATCH, 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 masalah ini? Dengan VLOOKUP, Anda harus mengetahui nomor kolom yang berisi nilai yang dikembalikan. Meskipun ini mungkin tidak tampak menantang, itu bisa merepotkan ketika Anda memiliki tabel besar dan harus menghitung jumlah kolom. Selain itu, jika menambahkan/menghapus kolom dalam tabel, Anda harus menceritakan dan memperbarui argumen col_index_num . Dengan INDEX dan MATCH, tidak ada penghitungan yang diperlukan karena kolom pencarian berbeda dari kolom yang memiliki nilai yang dikembalikan.
-
Dengan INDEX dan MATCH, 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—INDEX dan MATCH akan berfungsi jika nilai pencarian Anda berada di kolom pertama, yang terakhir, atau di mana saja di antaranya.
-
INDEX dan MATCH menawarkan fleksibilitas membuat referensi dinamis ke kolom yang berisi nilai yang dikembalikan. Artinya, Anda dapat menambahkan kolom ke tabel tanpa merusak INDEX dan MATCH. Di sisi lain, VLOOKUP memutuskan jika Anda perlu menambahkan kolom ke tabel—karena membuat referensi statis ke tabel.
-
INDEX dan MATCH menawarkan lebih banyak fleksibilitas dengan kecocokan. INDEX dan MATCH 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 mengasumsikan secara default bahwa kolom pertama dalam array tabel diurutkan menurut abjad, dan anggap tabel Anda tidak disiapkan dengan cara itu, VLOOKUP akan mengembalikan kecocokan terdekat pertama dalam tabel, yang mungkin bukan data yang Anda cari.
Sintaks
Untuk menyusun sintaks untuk INDEX/MATCH, Anda perlu menggunakan argumen array/referensi dari fungsi INDEX dan menumpuk sintaks MATCH di dalamnya. Ini mengambil formulir:
=INDEX(array atau referensi, MATCH(lookup_value,lookup_array,[match_type])
Mari kita gunakan INDEX/MATCH 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 MATCH(Kale, yang berada di suatu tempat dalam array B2:B10, di mana nilai yang dikembalikan adalah nilai pertama yang terkait dengan Kale))
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
Ketika argumen range_lookup FALSE—dan VLOOKUP tidak dapat menemukan kecocokan persis dalam data Anda—argumen tersebut mengembalikan kesalahan #N/A.
Solusi: Jika Anda yakin data yang relevan ada di lembar bentang Anda dan VLOOKUP tidak menangkapnya, luangkan waktu untuk memverifikasi bahwa sel yang dirujuk tidak memiliki spasi tersembunyi atau karakter non-cetak. Pastikan juga bahwa sel mengikuti tipe data yang benar. Misalnya, sel dengan angka harus diformat sebagai Angka, dan bukan Teks.
Pertimbangkan juga untuk menggunakan fungsi CLEAN atau TRIM 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.
Solusi:
-
Perbaiki nilai pencarian yang diperlukan.
-
Jika Anda tidak bisa mengubah nilai pencarian dan membutuhkan fleksibilitas yang lebih besar dengan nilai yang cocok, pertimbangkan untuk menggunakan INDEX/MATCH, 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 kolom pencarian Anda tidak diurutkan dalam urutan naik (A-Z) —Anda akan melihat kesalahan #N/A.
Solusi:
-
Ubah fungsi VLOOKUP untuk mencari kecocokan yang tepat. Untuk melakukannya, atur argumen range_lookup ke FALSE. Tidak diperlukan pengurutan 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 titik mengambang.) Excel tidak bisa menyimpan angka dengan titik mengambang yang sangat besar, sehingga agar fungsi berfungsi dengan benar, angka titik mengambang harus dibulatkan ke 5 tempat desimal.
Solusi: Perpendek angka dengan membulatkannya ke atas menjadi lima angka di sebelah kanan titik desimal dengan fungsi ROUND.
Perlu bantuan lainnya?
Anda selalu dapat bertanya kepada ahli di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.
