Fungsi VLOOKUP adalah salah satu fungsi pencarian di Excel yang digunakan untuk menemukan dan mengambil nilai dalam suatu tabel berdasarkan referensi.
Artikel ini mengulas rumus VLOOKUP, cara menggunakannya, serta contoh aplikasinya dalam berbagai skenario analisis data.
File: fungsi-vlookup-excel.xlsx, vlookup-sumber.xlsx.
Apa Itu Fungsi VLOOKUP di Excel?
Fungsi VLOOKUP mencari suatu nilai di kolom terkiri range data, lalu mengembalikan nilai dari kolom lain yang berada di baris yang sama dengan nilai yang ditemukan.
Sintaks VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
- lookup_value (wajib): Nilai yang dicari, dapat berupa angka, teks, atau referensi cell.
- table_array (wajib): Rentang data tempat pencarian dilakukan.
- col_index_num (wajib): Nomor kolom dalam table_array dari mana nilai akan diambil.
- range_lookup (opsional):
- TRUE (default): Mencari nilai terbesar yang lebih kecil atau sama dengan lookup_value (data harus diurutkan secara ascending).
- FALSE: Mencari nilai persis dengan lookup_value.
Hal Penting Mengenai VLOOKUP
- VLOOKUP tidak dapat melakukan pencarian ke arah kiri.
- Rumus mengembalikan #N/A jika nilai yang dicari tidak ditemukan.
- Jika col_index_num lebih besar dari jumlah kolom dalam table_array, hasilnya adalah #REF!.
- Saat range_lookup bernilai TRUE (pencarian rentang):
-
- Data harus terurut secara ascending.
- Jika lookup_value tidak ditemukan, Excel mengambil nilai tertinggi yang lebih kecil dari lookup_value.
- Jika range_lookup bernilai FALSE (pencarian eksak):
- Tidak memerlukan data berurutan.
Baca: Mengapa Rumus VLOOKUP Tidak Berfungsi.
Contoh Penggunaan VLOOKUP di Excel
Rumus VLOOKUP Sederhana
=VLOOKUP("Sylvia"; A2:C6; 2)Sylvia→ Nilai yang dicari.A2:C6→ Data tempat pencarian dilakukan.2→ Ambil nilai dari kolom ke-2 dalam A2:C6.
Jika Sylvia ditemukan, fungsi mengembalikan nilai di kolom kedua sesuai baris di mana Sylvia berada (2846000000).
VLOOKUP Beda Sheet
Rumus berikut mengambil data sheet yang berbeda.
=VLOOKUP(A2; '01'!A2:C6; 2)'01'!A2:C6→ Pencarian dilakukan di sheet berbeda, yaitu 01, sementara rumus berada di sheet 02.
VLOOKUP Beda File Excel
VLOOKUP juga dapat melakukan pencarian saat rumus dan sumber data berbeda file atau workbook.
=VLOOKUP(A2; '[vlookup-sumber.xlsx]Sumber'!$A$2:$C$6; 2)Pastikan file sumber terbuka saat mengeksekusi rumus ini.
Baca: Rumus Excel dengan Referensi Eksternal.
Perbedaan VLOOKUP TRUE dan FALSE
Perbedaan utama VLOOKUP dengan lookup_range TRUE dan FALSE adalah TRUE melakukan pencarian secara kira-kira dan FALSE secara eksak.
- Rumus mengembalikan #N/A karena tidak ada nilai terbesar yang lebih kecil dari Dia di lookup_array.
=VLOOKUP(A9; A2:C6; 2; TRUE) - Rumus mengembalikan 961.000 karena Mia adalah nilai terbesar di lookup_array yang lebih kecil dari Nia.
=VLOOKUP(A10; A2:C6; 2; TRUE) - Rumus mengembalikan #N/A karena tidak ada nilai Nia dengan range_lookup
=VLOOKUP(A11; A2:C6; 2; FALSE) - Rumus mengembalikan nilai penjualan Ria, yaitu 950.000.000.
=VLOOKUP(A12; A2:C6; 2; FALSE)
Menggabungkan VLOOKUP dengan Fungsi Lain
Kombinasi VLOOKUP dan IF
Rumus menentukan komisi berdasarkan besaran penjualan.
=IF(VLOOKUP(A9; A2:C6; 3; FALSE)>3000000000; VLOOKUP(A9; A2:C6; 3; FALSE)*1,5%; VLOOKUP(A9; A2:C6; 3; FALSE)*1,25%)
VLOOKUP dan AND
Menentukan bonus jika penjualan Hardiansyah setiap bulannya lebih besar dari Rp 3.000.000.000.
=IF(AND(VLOOKUP(A9; A2:C6; 2; FALSE)>3000000000; VLOOKUP(A9; A2:C6; 3; FALSE)>3000000000); 10000000; 0)
Penggunaan VLOOKUP dengan Wildcard
Wildcard adalah pencarian menggunakan karakter-karakter istimewa.
- Tanda tanya (?) mencari satu karakter di dalam teks.
- Tanda asterisk (*) mencari satu atau lebih karakter di dalam teks.
- Tanda tilde (~) menghilangkan kemampuan satu tanda tanya atau tanda asterisk yang ditulis setelahnya.
| Rumus | Penjelasan |
|---|---|
|
Mencari teks yang mengandung an. |
|
Mencari teks dengan karakter sebelum an. |
Jika hasil pencarian lebih dari satu, VLOOKUP akan mengembalikan yang pertama ditemukan.
Baca: Menggunakan Wildcard di Excel.
VLOOKUP dengan LEFT, MID, dan RIGHT
Baik LEFT, MID, dan RIGHT berfungsi untuk mengambil sebagian teks sebagai kunci pencarian.
- Menggunakan 2 karakter awal kode komisi untuk mencari persentase komisi.
=VLOOKUP(LEFT(A7; 2); $A$2:$B$4; 2; FALSE)*C7 - Menggunakan karakter ke-3 kode untuk menghitung bonus.
=VLOOKUP(MID(A7; 3; 1); $D$2:$E$4; 2)*D7 - Menggunakan 2 karakter terakhir kode untuk mendapatkan komisi referal.
=VLOOKUP(RIGHT(A7; 2); $G$2:$H$3; 2)
Error Handling dengan VLOOKUP dan IFERROR
VLOOKUP dapat mengembalikan error berupa #N/A atau #REF!. Untuk mencegahnya, gunakan IFERROR.
=IFERROR(VLOOKUP(A8; A2:B4; 2); 0) * C8Menampilkan 0 jika pencarian gagal.
Penutup
Fungsi VLOOKUP di Excel adalah alat yang ampuh untuk mencari dan mengambil data berdasarkan referensi dalam tabel. Dengan memahami sintaks, perbedaan TRUE/FALSE, serta kombinasi dengan fungsi lain, kita dapat mengoptimalkan pekerjaan sehari-hari.