Rumus VLOOKUP termasuk kedalam rumus yang paling populer dan paling banyak digunakan didalam Excel. Sebagai pengguna, tentunya kita harus bisa menguasi rumus VLOOKUP ini, agar bisa bekerja dengan cepat dan efisien.
Fungsi dari rumus Vlookup ini digunakan untuk mengambil suatu nilai dari table data lain berdasarkan kata kunci tertentu yang dijadikan acuan. V pada kata Vlookup mengacu kepada jenis table yang digunakan acuan yang berarti vertical.
Selain VLOOKUP ada juga rumus lain yaitu HLOOKUP yang berarti jenis table yang digunakan berbentuk Horizontal. Untuk melihat perbedaan keduanya, bisa dilihat pada gambar dibawah ini.
Sintaksis Rumus Vlookup
Sintaksis dasar dari Formula vlookup adalah:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Paremeter pertama Lookup_value, pada bagian ini diisi dengan Nilai acuan yang akan kita cari pada Table Array.
Parameter kedua ada Table_array, pada bagian ini diisi dengan Range table atau database dimana nilai acuan dan hasil berada.
Ketiga ada Col_index_num, ini adalah Index angka dari urutan kolom dimana kolom hasil berada. dihitung dari kolom acuan
Keempat Range_lookup, ini bersifat optional artinya boleh diisi boleh tidak. Tetapi untuk nilai default ketika range lookup ini tidak diisi akan bernilai TRUE.
Nilai TRUE berarti pencarian Lookup berdasarkan nilai yang mendekati sedangkan jika FALSE nilai yang dicari harus sama persis.
semua parameter diatas, bisa di baca lengkap di dokumentasi Mircosoft tentang fungsi vlookup.
TRUE atau FALSE ini bisa juga diisi dengan 1 atau 0. (1=True, 0=False)
Contoh Penggunaan Rumus VLOOKUP
Sebelum kita masuk kedalam contoh sederhana rumus VLOOKUP, seperti yang kita tahu bahwa rumus ini membutuhkan sebuah table array atau table referensi sebagian acuan data Lookup. Berikut ini adalah contoh table referensi yang akan digunakan dalam contoh penggunaan vlookup kali ini.
Bisa kita lihat kolom ID, kolom ini berisi data unik dan kolom ID ini yang akan menjadi acuan dalam rumus VLOOKUP nantinya.
Berikut adalah contoh pengunaan rumus vlookup pada excel.
Rumus yang digunakan untuk mencari stok berdasarkan ID yang ada di G5 adalah
=VLOOKUP(G5,B5:E12,3,FALSE)
Penjelasan rumus diatas adalah sebagai berikut
- G5 adalah nilai acuan yang akan di cari di Table Data product berdasarkan kolom ID
- B5:E12 adalah Area dari table Data product diawali Kolom ID
- 3 adalah Index kolom dari kolom Stock (bisa dilihat di gambar)
- FALSE adalah metode pencarian Nilai acuan. False atau 0 artinya harus sama persis.
Untuk pengisian rumus VLOOKUP ke lebih dari satu cell, bisa ditambahkan Range Absolute pada Table Array, agar pada saat melakukan copy rumus vlookup, Range Table tidak berubah.
Untuk mmbuat Range Table Array menjadi Absolut bisa tekan F4 atau tambahkan simbol $ secara manual. Materi lebih detail tentang Range absolute ini bisa di baca di fungsi simbol $ dalam rumus Excel.
Dengan Table Array seperti diatas, ketika rumus di copy atau di drag ke Cell lain, table array tidak akan berubah referensinya (Absolut).
Range Lookup TRUE
Jika tadi sudah mencontohkan fungsi vlookup dengan kondisi FALSE, kali ini kita bahas kondisi VLOOKUP dengan Range Lookup TRUE atau Parameter Range Lookup di kosongkan.
Bagian ini yang banyak tidak diketahui oleh pemula, biasanya pemula dalam menuliskan rumus Vlookup tidak menyertakan kondisi Range Lookup sehingga jika di kosongkan akan dianggap TRUE.
Misalnya seperti penulisan rumus dibawah ini
=VLOOKUP(G5,B5:E12,3)
Penulisan rumus seperti diatas, sama artinya seperti dibawah ini
=VLOOKUP(G5,B5:E12,3,TRUE)
Formula dengan range Lookup kondisi TRUE akan menghasilkan Nilai yang mendekati, bukan yang sama persis. Untuk contoh pengunaannya bisa dilihat pada gambar dibawah ini.
Rumus Vlookup Beda Sheet
Untuk melakukan lookup data pada sheet yang berbeda, rumus yang digunakan tidak jauh berbeda. Rumus dasarnya masih sama dengan rumus VLOOKUP diatas, yang membedakan adalah Alamat dari Table Arraynya.
Dimisalkan, Table product yang sudah kita buat sebelumnya berada pada Sheet yang bernama Database. Untuk melakukan lookup data dari Sheet lain, hanya perlu menambahkan kata ‘database!’ pada alamat Table Array, atau dengan cara mengarahkan table dengan Mouse.
Contoh pengunaanya rumus vlookup beda sheet, bisa dilihat pada gambar dibawah ini.
Kata Database dalam table array adalah nama sheet tempat Data table berada, cara menambahkannya bisa ditambahkan secara manual atau secara otomatis dengan cara memilih menggunakan Mouse.
Rumus Vlookup Beda File
Setelah mengetahui rumus untuk berbeda sheet, rumus vlookup berbeda File tidak jauh berbeda. Yang membedakannya hanyalah penulisan table dilengkapi dengan nama file.
Untuk memudahkan pembuatan rumus vlookup beda file, kedua file harus dalam keadaan terbuka. Saat memilih table referensi arahkan mouse pada Table yang sudah disediakan.
Sebagai contoh, table data product diatas disimpan dalam nama File TableData.xlsx. Maka rumus Vlookupnya akan berubah menjadi seperti dibawah ini
=VLOOKUP(G5,'[TableData.xlsx]Database'!$B$5:$E$12,3,FALSE)
Rumus Vlookup Tanpa Table Array
Untuk keperluan tertentu, rumus vlookup ini bisa juga tanpa menggunakan table yang ada pada cell. Tetapai table array yang digunakan dibuat langsung didalam rumus.
Seperti misalnya formula yang digunakan untuk mencari perdikat dari sebuah nilai. Contoh rumus yang digunakan adalah sebagai berikut.
=VLOOKUP(B3,{1,"D";60,"C";70,"B";90,"A"},2,TRUE)
Data yang ada diantara { } itu adalah data Array, sama seperti yang ada pada Table fisik yang ada pada Cells. Untuk pembuatan Array ini ada perbedaan antara Excel bahasa Indonesia dengan Bahasa Inggris.
| B. Indonesia | Inggris | |
| Vertical Array | ; | , |
| Horizontal Array | \ | ; |
Selain dengan Array, rumus vlookup juga bisa di kombinasikan dengan fungsi lain, sesuai dengan keperluannya.
Kesalahan Yang Sering Terjadi
Beberapa pemula yang baru memahami fungsi ini tentu akan mengalami kendala sehingga menghasilkan kesalahan. Berikut ini adalah beberapa kesalahan yang sering di muncul dalam penggunaan rumus Vlookup.
Salah mengembalikan Nilai
Masalah pertama yang sering terjadi adalah ketidak sesuaian hasil rumus dengan nilai yang diharapkan.
Biasanya masalah ini terjadi karena masalah range_lookup diisi TRUE atau dihilangkan. Untuk kondisi TRUE, kolom acuan harus diurutkan menurut abjad. Jika kolom acuan tidak diurutkan, nilai yang dikembalikan mungkin tidak akan sesuai dengan yang diharapkan.
Jika tidak berurutan bisa gunakan FALSE untuk mencocokan Nilai dengan kondisi sama persis.
#N/A Nilai Acuan Tidak Ditemukan
Error #N/A adalah error yang ditimbulkan akibat pencarian nilai acuan pada table referensi tidak ditemukan.
Tidak ditemukan sini mengacu juga terhadap perbedaan type Data. Jika sudah sangat yakin bahwa nilai acuan yang dicari berada pada table Array, harus dipastikan kembali kedua cell memiliki type data yang sama dan berIsi sama persis tidak ada kelebihan spasi atau semacamnya.
Contoh Nilai yang sama persis tetapi memiliki type data yang berbeda bisa dlihat pada gambar dibawah ini.
#N/A Kesalahan Vlookup ke Kiri
Yang harus diketahui dari sifat Rumus vlookup adalah tidak bisa melakukan pencarian Nilai yang berada pada sebelah kiri dari Nilai Acuan.
Ya, fungsi ini hanya bisa melakukan lookup value ke sebalah kanan saja. jika ini dilakukan ini juga akan menyebabkan Error #N/A pada rumus Vlookup.
Berikut ini adalah contoh kesalahan penggunaan vlookup ke kiri.
Jika memiliki data seperti ini, bisa menggunakan fungsi alternatif seperti Index dan Match atau menggunakan kombinasi vlookup untuk lookup kekiri yang bisa di baca di artikel cara rumus vlookup ke kiri.
Menghasilkan Error #REF!
Masalah ini terjadi Jika col_index_num lebih besar dari jumlah kolom yang ada dalam tabel-array. Jadi harus disesuaikan dengan jumlah kolom yang ada di table.
Untuk mengetahui jenis- jenis error yang lain yang ada pada Microsoft Excel, bisa dibaca di artikel Mengenal 9 Jenis Error yang ada pada Excel
Leave a Reply