Fungsi VLOOKUP Excel adalah salah satu rumus paling populer di Excel untuk mencari data secara vertikal berdasarkan nilai kunci. Dengan VLOOKUP, kita bisa mengambil informasi dari kolom lain dalam sebuah tabel, dengan menyebutkan nilai yang ingin dicari.
Artikel ini membahas sintaks, cara kerja, contoh penggunaan, serta tips penting agar rumus VLOOKUP bekerja akurat dan efisien.
File berikut adalah sumber dari mana semua screenshot di artikel ini berasal.
File: fungsi-vlookup-excel.xlsx.
Apa Itu Fungsi VLOOKUP di Excel?
Fungsi VLOOKUP (vertical lookup) adalah fungsi Excel yang memperbolehkan pengguna untuk mengambil informasi dari kolom pada range atau array lain.
Sintaks VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Parameter:
- lookup_value (wajib): Nilai kunci yang ingin dicari. Nilai bisa teks, angka, boolean, atau tanggal.
- table_array (wajib): Rentang data tempat pencarian dilakukan
- col_index_num (wajib): Indeks kolom di table_array dari mana hasil dikembalikan. Kolom pertama memiliki nilai indeks 1.
- range_lookup (opsional): Nilai boolean yang menentukan mode pencarian.
- TRUE (default): Mencari berdasarkan kemiripan.
- FALSE: Mencari yang sama persis.
Kapan Menggunakan Rumus VLOOKUP
Berikut beberapa contoh kasus penggunaan VLOOKUP:
- Mencari harga barang berdasarkan kode
- Mengambil nilai siswa berdasarkan ID
- Menentukan potongan harga berdasarkan rentang belanja
- Menggabungkan data dari dua tabel berbeda
- Membuat laporan dinamis berdasarkan input pengguna
Hal Penting Tentang Rumus VLOOKUP
- Excel selalu mencari lookup_value di kolom pertama table_array.
- VLOOKUP akan mengembalikan nilai berdasarkan kecocokan pertama yang ditemui. Usahakan menghindari duplikasi nilai.
- Jika nilai yang dicari tidak ditemukan, VLOOKUP akan mengembalikan #N/A.
- Pastikan range_lookup bernilai FALSE untuk mencari nilai identik.
- Jika range_lookup bernilai TRUE:
- Data kolom pencarian harus terurut secara ascending.
- Excel akan mencari nilai sama persis atau nilai terbesar yang lebih kecil dari lookup_value.
- Pastikan data pada kolom pencarian tidak memiliki karakter tambahan yang tidak terlihat, seperti spasi di akhir nilai.
- Jika rumus akan disalinkan ke cell-cell lain, gunakan table_array dengan referensi absolut.
- Untuk pencarian horizontal, gunakan HLOOKUP. Untuk pencarian lebih fleksibel, gunakan XLOOKUP.
Cara Menggunakan Rumus VLOOKUP di Excel
Mencari Nilai Siswa Berdasarkan Kode
Rumus berikut mengambil data nilai siswa berdasarkan nomor identitas.
=VLOOKUP(E2; A2:C6; 3; FALSE)Berdasarkan rumus di atas:
E2(lookup_value): Nilai yang dicari.A2:C6(table_array): Rentang data di mana nilai cell E2 akan dicari dan nilai kembalian berasal.3(col_index_num): Indeks kolom dari A2:C6 dari mana nilai kembalian berasal.FALSE(range_lookup): Mencari nilai sama persis.
Mencari Harga Barang Berdasarkan Kode
Rumus berikut mengembalikan harga barang berdasarkan kode.
=VLOOKUP(A8; $A$2:$B$4; 2; FALSE)table_array menggunakan referensi absolut agar tidak berubah saat rumus disalinkan ke bawah.
Mencari Diskon Belanja
Sebuah toko memberikan promo potongan harga untuk pembelanjaan:
- Di bawah Rp 100.000: 0%
- Minimal Rp 100.000: 10%
- Minimal Rp 200.000: 15%
- Minimal Rp 300.000: 20%
- Minimal Rp 400.000: 25%
Rumus mencari potongan harga untuk pembelanjaan sebesar Rp 328.000 dan mengembalikan 20%. Sebabnya, Rp 300.000 adalah nilai terbesar yang lebih kecil dari Rp 328.000.
=VLOOKUP(D2; A2:B6; 2; TRUE)
Perhatikan:
- range_lookup bernilai TRUE. Artinya, pencarian berdasarkan perkiraan. Jika range_lookup FALSE, rumus mengembalikan #N/A.
- Rentang belanja terurut secara ascending.
Menampilkan Banyak Kolom Sekaligus (2 atau Lebih)
Rumus di bawah mengembalikan potongan harga dan poin sekaligus. Rumus menggunakan array {2; 3} bagi col_index_num yang mewakili kolom ke-2 dan ke-3 di table_array.
=VLOOKUP(F1; A2:C6; {2;3}; TRUE)Untuk menampilkan data secara horizontal, gunakan fungsi TRANSPOSE.
Mengatasi Error #N/A VLOOKUP
Saat nilai yang dicari tidak ditemukan, VLOOKUP akan menampilkan pesan error #N/A. Gunakan fungsi IFNA untuk menangkap #N/A dan menggantikannya dengan angka nol atau nilai lain yang sesuai.
=IFNA(VLOOKUP(A7; $A$2:$B$4; 2; FALSE); 0)
Menggunakan VLOOKUP dengan Wildcard
Wildcard adalah metode untuk mencari kata yang mengandung teks tertentu.
Rumus di bawah mencari penjual yang bernama Dewi. VLOOKUP akan mengembalikan nilai penjualan dari Dewi pertama yang ditemui.
=VLOOKUP("*" & D2 & "*"; A2:B6; 2; FALSE)
Menggunakan VLOOKUP Bersarang
VLOOKUP bersarang atau bertingkat digunakan ketika data berada di 2 rentang atau lebih.
Pada rumus di bawah:
- VLOOKUP tersarang mencari kategori berdasarkan kode.
- VLOOKUP induk mencari departemen berdasarkan kategori hasil kembalian VLOOKUP tersarang.
=VLOOKUP(VLOOKUP(A9; A2:B6; 2; FALSE); D2:E6; 2; FALSE)
Rumus VLOOKUP dengan Kolom Fleksibel
Rumus VLOOKUP mengharuskan penulisan indeks kolom tetap di dalam rumus. Perubahan terhadap struktur kolom table_array dapat mengakibatkan nilai col_index_num tidak lagi relevan.
Rumus di bawah menggunakan MATCH sebagai col_index_num. MATCH menentukan posisi kolom berdasarkan nama, bukan angka tetap.
=VLOOKUP(A7; A2:D4; MATCH("Harga"; A1:D1; 0); FALSE)
Perbedaan VLOOKUP FALSE DAN TRUE
| Parameter | FALSE (Exact Match) | TRUE (Approximate Match) |
|---|---|---|
| Tujuan | Mencari nilai yang sama persis | Mencari nilai terdekat dalam rentang |
| Contoh Kasus | Harga barang berdasarkan kode, ID siswa, divisi karyawan | Potongan harga berdasarkan belanja, tarif pajak, nilai kualitatif |
| Syarat Data | Tidak perlu diurutkan | Kolom pencarian harus diurutkan secara ascending |
| Jika Tidak Cocok | Mengembalikan #N/A | Mengembalikan nilai terbesar yang lebih kecil dari lookup_value |
| Risiko Umum | Salah ketik atau spasi tersembunyi → #N/A | Data tidak terurut → hasil tidak akurat |
Singkatnya, gunakan FALSE saat butuh hasil yang pasti. Gunakan TRUE saat bekerja dengan rentang nilai atau kategori bertingkat.
Mengapa Rumus VLOOKUP Tidak Bekerja?
- Error #N/A: Nilai yang dicari tidak ditemukan.
- Error #REF!: col_index_num lebih besar dari jumlah kolom table_array.
- Error #VALUE!: col_index_num lebih kecil dari 1.
- Hasil tidak sesuai: Data kolom pencarian tidak terurut secara ascending saat range_lookup bernilai TRUE.
- Tipe data tidak sesuai: lookup_value memiliki tipe data berbeda dengan data di kolom pertama table_array, baik angka terformat sebagai teks atau sebaliknya.
Baca: Mengapa Rumus VLOOKUP Tidak Bekerja.
Tips Tambahan
- Gunakan INDEX + MATCH untuk pencarian lebih fleksibel dan kebal terhadap perubahan struktur tabel.
- Gunakan XLOOKUP di Excel 365 untuk pencarian dua arah, default error handling, dan pencarian horizontal.
- Untuk pencarian dinamis berdasarkan input pengguna, kombinasikan VLOOKUP dengan IF, CHOOSE, atau INDIRECT.
Kesimpulan
Fungsi VLOOKUP di Excel berguna, terutama saat kita bekerja dengan tabel besar dan ingin mengambil informasi berdasarkan nilai kunci.
Dan jika kebutuhan berkembang di atas kemampuan VLOOKUP, gunakan alternatif lebih canggih seperti XLOOKUP atau INDEX-MATCH.