Rumus VLOOKUP adalah salah satu rumus excel yang paling populer dan berguna untuk melakukan pencarian data dalam tabel atau rentang. Rumus VLOOKUP memungkinkan Anda untuk mencari nilai tertentu di kolom pertama tabel, dan mengembalikan nilai yang sesuai dari kolom lain di baris yang sama. Rumus VLOOKUP sangat membantu untuk menghemat waktu dan usaha dalam mengelola data yang besar dan kompleks.
Namun, banyak pengguna excel yang masih bingung atau kesulitan dalam menggunakan rumus VLOOKUP, baik karena kurang memahami sintaks dan argumen rumus, atau karena menghadapi masalah dan kesalahan yang sering terjadi saat menggunakan rumus VLOOKUP. Oleh karena itu, artikel ini akan membahas secara lengkap dan mendalam tentang cara menggunakan rumus VLOOKUP excel untuk pencarian data yang cepat dan akurat, mulai dari pengertian, sintaks, argumen, contoh, hingga tips dan trik untuk mengatasi masalah dan kesalahan yang sering terjadi saat menggunakan rumus VLOOKUP.
Pengertian Rumus VLOOKUP Excel
Rumus VLOOKUP adalah singkatan dari Vertical Lookup, yang berarti pencarian vertikal. Rumus VLOOKUP digunakan untuk mencari nilai vertikal dalam sebuah tabel atau rentang data. Rumus VLOOKUP termasuk dalam kategori fungsi Lookup and Reference, yang berfungsi untuk mencari dan merujuk data dalam excel.
Rumus VLOOKUP bekerja dengan cara mencocokkan nilai yang dicari (lookup value) dengan nilai di kolom pertama tabel atau rentang data (table array), dan mengembalikan nilai yang sesuai dari kolom lain di baris yang sama (column index number). Rumus VLOOKUP juga dapat menentukan apakah pencarian dilakukan dengan kecocokan yang persis (exact match) atau yang mendekati (approximate match), dengan menggunakan argumen range lookup.
Sintaks dan Argumen Rumus VLOOKUP Excel
Sintaks atau rumus dasar untuk menggunakan rumus VLOOKUP excel adalah sebagai berikut:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Rumus VLOOKUP excel memiliki empat argumen, yaitu:
- lookup_value: nilai yang ingin dicari dalam tabel atau rentang data. Nilai ini harus berada di kolom pertama tabel atau rentang data. Nilai ini dapat berupa angka, teks, tanggal, atau referensi sel. Nilai ini juga dapat dihasilkan oleh rumus excel lainnya.
- table_array: tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini harus memiliki setidaknya dua kolom, dan kolom pertamanya harus berisi lookup_value. Tabel atau rentang data ini dapat berupa referensi sel, nama rentang, atau nama tabel.
- col_index_num: nomor kolom dalam tabel atau rentang data yang berisi nilai yang akan dikembalikan oleh rumus VLOOKUP. Nomor kolom ini dihitung dari kiri ke kanan, dimulai dari 1 untuk kolom pertama. Nomor kolom ini harus berupa angka positif, dan tidak boleh lebih besar dari jumlah kolom dalam tabel atau rentang data.
- [range_lookup]: nilai logika yang menentukan apakah pencarian dilakukan dengan kecocokan yang persis atau yang mendekati. Nilai ini bersifat opsional, dan dapat berupa TRUE atau FALSE. Jika tidak ditentukan, nilai ini akan dianggap TRUE.
Argumen [range_lookup] memiliki pengaruh yang besar terhadap hasil yang dikembalikan oleh rumus VLOOKUP. Berikut adalah penjelasan dan perbedaan antara menggunakan TRUE atau FALSE untuk argumen [range_lookup]:
- Jika [range_lookup] bernilai TRUE, maka rumus VLOOKUP akan mencari nilai yang sama atau yang mendekati dengan lookup_value. Jika ada nilai yang sama persis, maka rumus VLOOKUP akan mengembalikan nilai yang sesuai dari kolom yang ditentukan. Jika tidak ada nilai yang sama persis, maka rumus VLOOKUP akan mengembalikan nilai yang sesuai dari kolom yang ditentukan untuk nilai terbesar yang lebih kecil dari lookup_value. Untuk menggunakan [range_lookup] bernilai TRUE, kolom pertama tabel atau rentang data harus diurutkan secara menaik, baik secara numerik maupun alfabetis. Jika tidak, hasil yang dikembalikan oleh rumus VLOOKUP mungkin tidak akurat atau salah.
- Jika [range_lookup] bernilai FALSE, maka rumus VLOOKUP akan mencari nilai yang sama persis dengan lookup_value. Jika ada nilai yang sama persis, maka rumus VLOOKUP akan mengembalikan nilai yang sesuai dari kolom yang ditentukan. Jika tidak ada nilai yang sama persis, maka rumus VLOOKUP akan mengembalikan #N/A, yang berarti tidak ada hasil yang ditemukan. Untuk menggunakan [range_lookup] bernilai FALSE, kolom pertama tabel atau rentang data tidak perlu diurutkan, dan dapat berisi nilai apa saja.
Contoh Penggunaan Rumus VLOOKUP Excel
Berikut adalah beberapa contoh penggunaan rumus VLOOKUP excel untuk pencarian data yang cepat dan akurat, dengan menggunakan data berikut sebagai tabel atau rentang data:
Table
ID Nama Jabatan Gaji
101 Andi Manager 10.000.000
102 Budi Supervisor 7.500.000
103 Cici Staff 5.000.000
104 Dedi Staff 5.000.000
105 Eka Supervisor 7.500.000
Contoh 1: Mencari nama karyawan berdasarkan ID karyawan, dengan menggunakan kecocokan yang persis.
Misalkan kita ingin mencari nama karyawan yang memiliki ID 103, dan menampilkan hasilnya di sel G2. Maka rumus VLOOKUP yang dapat kita gunakan adalah sebagai berikut:
=VLOOKUP(103,A2:D6,2,FALSE)
Penjelasan rumus:
- lookup_value adalah 103, yaitu nilai yang ingin dicari di kolom pertama tabel atau rentang data.
- table_array adalah A2:D6, yaitu tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini memiliki empat kolom, dan kolom pertamanya berisi ID karyawan.
- col_index_num adalah 2, yaitu nomor kolom dalam tabel atau rentang data yang berisi nilai yang akan dikembalikan oleh rumus VLOOKUP. Dalam hal ini, kolom kedua adalah kolom nama karyawan.
- [range_lookup] adalah FALSE, yaitu nilai logika yang menentukan bahwa pencarian dilakukan dengan kecocokan yang persis. Dengan demikian, rumus VLOOKUP hanya akan mengembalikan nama karyawan yang memiliki ID yang sama persis dengan 103.
Hasil rumus:
Cici
Contoh 2: Mencari gaji karyawan berdasarkan nama karyawan, dengan menggunakan kecocokan yang mendekati.
Misalkan kita ingin mencari gaji karyawan yang bernama Dedi, dan menampilkan hasilnya di sel H2. Maka rumus VLOOKUP yang dapat kita gunakan adalah sebagai berikut:
=VLOOKUP(“Dedi”,B2:D6,3,TRUE)
Penjelasan rumus:
-
- lookup_value adalah “Dedi”, yaitu nilai yang ingin dicari di kolom pertama tabel atau rentang data. Nilai ini ditulis dalam tanda kutip karena merupakan teks.
- table_array adalah B2:D6, yaitu tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini memiliki tiga kolom, dan kolom pertamanya berisi nama karyawan. Perhatikan bahwa tabel atau rentang data ini tidak termasuk kolom ID karyawan, karena kolom tersebut tidak relevan dengan pencarian yang ingin dilakukan.
dengan menggunakan data berikut sebagai tabel atau rentang data:
Table
ID Nama Jabatan Gaji
101 Andi Manager 10.000.000
102 Budi Supervisor 7.500.000
103 Cici Staff 5.000.000
104 Dedi Staff 5.000.000
105 Eka Supervisor 7.500.000
Contoh 3: Mencari jabatan karyawan berdasarkan ID karyawan, dengan menggunakan kecocokan yang persis.
Misalkan kita ingin mencari jabatan karyawan yang memiliki ID 105, dan menampilkan hasilnya di sel I2. Maka rumus VLOOKUP yang dapat kita gunakan adalah sebagai berikut:
=VLOOKUP(105,A2:D6,3,FALSE)
Penjelasan rumus:
• lookup_value adalah 105, yaitu nilai yang ingin dicari di kolom pertama tabel atau rentang data.
• table_array adalah A2:D6, yaitu tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini memiliki empat kolom, dan kolom pertamanya berisi ID karyawan.
• col_index_num adalah 3, yaitu nomor kolom dalam tabel atau rentang data yang berisi nilai yang akan dikembalikan oleh rumus VLOOKUP. Dalam hal ini, kolom ketiga adalah kolom jabatan karyawan.
• [range_lookup] adalah FALSE, yaitu nilai logika yang menentukan bahwa pencarian dilakukan dengan kecocokan yang persis. Dengan demikian, rumus VLOOKUP hanya akan mengembalikan jabatan karyawan yang memiliki ID yang sama persis dengan 105.
Hasil rumus:
Supervisor
Contoh 4: Mencari ID karyawan berdasarkan gaji karyawan, dengan menggunakan kecocokan yang mendekati.
Misalkan kita ingin mencari ID karyawan yang memiliki gaji sekitar 6.000.000, dan menampilkan hasilnya di sel J2. Maka rumus VLOOKUP yang dapat kita gunakan adalah sebagai berikut:
=VLOOKUP(6000000,D2:A6,-3,TRUE)
Penjelasan rumus:
• lookup_value adalah 6000000, yaitu nilai yang ingin dicari di kolom pertama tabel atau rentang data. Nilai ini ditulis tanpa tanda titik karena merupakan angka.
• table_array adalah D2:A6, yaitu tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini memiliki empat kolom, dan kolom pertamanya berisi gaji karyawan. Perhatikan bahwa tabel atau rentang data ini dibalik dari A2:D6, karena kita ingin mencari nilai di kolom terakhir, bukan kolom pertama. Tabel atau rentang data ini juga harus diurutkan secara menaik berdasarkan kolom pertama, yaitu gaji karyawan.
• col_index_num adalah -3, yaitu nomor kolom dalam tabel atau rentang data yang berisi nilai yang akan dikembalikan oleh rumus VLOOKUP. Nomor kolom ini negatif karena kita ingin mengambil nilai dari kolom yang berada di sebelah kiri kolom pertama, bukan di sebelah kanan. Dalam hal ini, kolom ketiga dari kiri adalah kolom ID karyawan.
• [range_lookup] adalah TRUE, yaitu nilai logika yang menentukan bahwa pencarian dilakukan dengan kecocokan yang mendekati. Dengan demikian, rumus VLOOKUP akan mengembalikan ID karyawan yang memiliki gaji yang sama atau yang mendekati dengan 6000000.
Hasil rumus:
102
Contoh 5: Mencari nama dan jabatan karyawan berdasarkan ID karyawan, dengan menggunakan rumus VLOOKUP bersarang.
Misalkan kita ingin mencari nama dan jabatan karyawan yang memiliki ID 104, dan menampilkan hasilnya di sel K2 dan L2. Maka rumus VLOOKUP yang dapat kita gunakan adalah sebagai berikut:
=VLOOKUP(104,A2:D6,2,FALSE) di sel K2
=VLOOKUP(104,A2:D6,3,FALSE) di sel L2
Penjelasan rumus:
• lookup_value adalah 104, yaitu nilai yang ingin dicari di kolom pertama tabel atau rentang data.
• table_array adalah A2:D6, yaitu tabel atau rentang data yang akan dicari oleh rumus VLOOKUP. Tabel atau rentang data ini memiliki empat kolom, dan kolom pertamanya berisi ID karyawan.
• col_index_num adalah 2 di sel K2, dan 3 di sel L2, yaitu nomor kolom dalam tabel atau rentang data yang berisi nilai yang akan dikembalikan oleh rumus VLOOKUP. Dalam hal ini, kolom kedua adalah kolom nama karyawan, dan kolom ketiga adalah kolom jabatan karyawan.
• [range_lookup] adalah FALSE, yaitu nilai logika yang menentukan bahwa pencarian dilakukan dengan kecocokan yang persis. Dengan demikian, rumus VLOOKUP hanya akan mengembalikan nama dan jabatan karyawan yang memiliki ID yang sama persis dengan 104.
Hasil rumus:
Dedi di sel K2
Staff di sel L2
Tips dan Trik Mengatasi Masalah dan Kesalahan Rumus VLOOKUP Excel
Rumus VLOOKUP excel dapat menjadi rumus yang sangat berguna dan efisien untuk pencarian data, tetapi juga dapat menjadi rumus yang rentan terhadap masalah dan kesalahan. Berikut adalah beberapa tips dan trik untuk mengatasi masalah dan kesalahan yang sering terjadi saat menggunakan rumus VLOOKUP excel:
-
-
- Pastikan argumen lookup_value, table_array, dan col_index_num sesuai dengan data yang ingin dicari dan dikembalikan. Jika argumen tersebut tidak sesuai, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
- Pastikan argumen [range_lookup] sesuai dengan jenis pencarian yang ingin dilakukan, yaitu kecocokan yang persis atau yang mendekati. Jika argumen [range_lookup] tidak sesuai, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
- Pastikan kolom pertama tabel atau rentang data diurutkan secara menaik jika menggunakan [range_lookup] bernilai TRUE. Jika tidak, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
- Pastikan tidak ada nilai duplikat di kolom pertama tabel atau rentang data jika menggunakan [range_lookup] bernilai FALSE. Jika ada, rumus VLOOKUP mungkin mengembalikan hasil yang tidak diinginkan, karena rumus VLOOKUP akan mengembalikan nilai yang sesuai dari kolom yang ditentukan untuk nilai pertama yang ditemukan.
- Pastikan tidak ada spasi atau karakter tambahan di lookup_value atau di kolom pertama tabel atau rentang data. Jika ada, rumus VLOOKUP mungkin mengembalikan #N/A, karena rumus VLOOKUP akan mencari nilai yang sama persis dengan lookup_value, termasuk spasi atau karakter tambahan.
- Pastikan format data pada lookup_value dan kolom pertama tabel atau rentang data sama. Jika tidak, rumus VLOOKUP mungkin mengembalikan #N/A, karena rumus VLOOKUP akan mencari nilai yang sama persis dengan lookup_value, termasuk format data.
- Pastikan tidak ada kesalahan referensi atau rumus pada lookup_value, table_array, atau col_index_num. Jika ada, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
- Pastikan tidak ada perubahan atau penghapusan data pada tabel atau rentang data yang mempengaruhi rumus VLOOKUP. Jika ada, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
- Pastikan tidak ada penguncian sel atau rentang yang menghalangi rumus VLOOKUP. Jika ada, rumus VLOOKUP mungkin mengembalikan #N/A, #REF!, #VALUE!, atau hasil yang salah.
-
