Tutorial Rumus Excel Vlookup untuk Mencari Data yang Sama

Ketika bekerja dengan data dalam jumlah besar, mencari informasi satu per satu tentu akan memakan waktu. Microsoft Excel menyediakan berbagai fungsi yang dapat membantu proses tersebut, salah satunya adalah VLOOKUP. Rumus ini dapat digunakan untuk mencari suatu nilai pada tabel referensi, kemudian mengambil informasi lain yang berada pada baris yang sama.

Sebelum mempelajari cara menggunakan VLOOKUP Excel, penting untuk memahami fungsi dan cara kerja dasarnya. VLOOKUP termasuk dalam kategori Lookup & Reference dan merupakan salah satu rumus Excel yang banyak digunakan dalam pekerjaan administratif, pengolahan data, hingga analisis sederhana.

Huruf “V” pada VLOOKUP merupakan singkatan dari Vertical, yang menunjukkan bahwa proses pencarian dilakukan secara vertikal pada kolom pertama sebuah tabel referensi. Setelah menemukan nilai yang dicari, Excel akan mengambil data dari kolom tertentu pada baris yang sama.

Dengan memahami VLOOKUP, kamu dapat menghemat waktu ketika harus mencocokkan dua tabel, misalnya mencocokkan data transaksi dengan daftar harga, mencari nama berdasarkan ID, mengambil kategori produk, atau menentukan tarif berdasarkan wilayah. Berikut adalah tutorial rumus Excel VLOOKUP yang bisa kamu ikuti penjelasannya di bawah ini, sahabat DQLab!

1. Fungsi Rumus VLOOKUP Excel

VLOOKUP atau Vertical Lookup adalah formula Excel yang digunakan untuk mencari nilai pada kolom pertama sebuah tabel, kemudian mengembalikan nilai dari kolom lain yang masih berada pada baris yang sama. Secara sederhana, cara kerja VLOOKUP dapat digambarkan seperti berikut:

Cari nilai → temukan baris yang sesuai → ambil data dari kolom yang ditentukan.

VLOOKUP dapat digunakan ketika kita memiliki sebuah nilai unik atau nilai tertentu yang dapat dijadikan acuan pencarian. Misalnya, kita memiliki ID produk dan ingin mengetahui nama atau harga produk tersebut dari tabel referensi.

Syntax VLOOKUP

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Ada empat komponen utama dalam rumus VLOOKUP:

a. Lookup_value

Lookup_value adalah nilai yang ingin dicari pada tabel referensi.

Nilai ini harus berada pada kolom paling kiri dari table_array yang digunakan oleh VLOOKUP.

Contohnya, jika ingin mencari data berdasarkan kode produk, maka kode produk tersebut harus menjadi kolom pertama pada tabel referensi.

b. Table_array

Table_array merupakan tabel atau range yang menjadi sumber data pencarian.

Bagian terpenting yang perlu diperhatikan adalah lookup_value harus berada pada kolom pertama table_array. Jika nilai yang ingin dicari berada di tengah atau di sebelah kanan tabel referensi, VLOOKUP tidak dapat melakukan pencarian secara langsung.

c. Col_index_num

Col_index_num menunjukkan nomor kolom pada table_array yang datanya ingin diambil.

Misalnya table_array terdiri dari lima kolom dan informasi yang ingin diambil berada di kolom kelima, maka col_index_num diisi dengan angka 5.

d. Range_lookup

Range_lookup menentukan jenis pencarian yang dilakukan oleh VLOOKUP.

Ada dua pilihan:

• TRUE: mencari nilai yang sama atau mendekati. Umumnya digunakan untuk pencarian berdasarkan rentang, seperti menentukan kategori berdasarkan skor atau tarif berdasarkan batas tertentu.

• FALSE: mencari nilai yang sama persis. Pilihan ini sering digunakan ketika mencocokkan ID, kode, nama, atau data tertentu.

Jika ingin mencari data yang benar-benar sama, FALSE biasanya menjadi pilihan yang lebih aman.

Contoh:

=VLOOKUP(A2,$E$2:$H$20,3,FALSE)

Artinya, Excel akan mencari nilai pada A2 di kolom pertama range E2, kemudian mengambil nilai dari kolom ketiga pada baris yang ditemukan.

Baca Juga: Bootcamp Data Analyst with Excel

2. Persiapan Data untuk Mencari Data yang Sama

Supaya lebih mudah memahami cara mencari data yang sama di Excel menggunakan VLOOKUP, kita dapat menggunakan contoh kasus pencarian biaya kirim berdasarkan alamat dan kategori pengiriman. Misalnya terdapat dua tabel:

• Tabel Tarif Ongkos Kirim, yang berisi provinsi, kabupaten, kecamatan, dan tarif pengiriman.

• Tabel Data Transaksi, yang berisi alamat pelanggan dan kategori pengiriman.

Pertanyaannya:

Berapakah biaya kirim masing-masing alamat jika pengiriman menggunakan kategori Reguler?

Pada kasus sederhana, kita mungkin hanya perlu mencocokkan satu nilai. Namun, dalam contoh ini alamat terdiri dari beberapa informasi, yaitu Provinsi, Kabupaten, dan Kecamatan.

VLOOKUP secara langsung hanya menggunakan satu lookup_value. Oleh karena itu, kita dapat membuat kolom bantuan (helper column) dengan menggabungkan beberapa informasi tersebut menjadi satu nilai pencarian. Mengapa perlu kolom bantuan?

Kolom bantuan berguna ketika kita harus mencocokkan beberapa kriteria sekaligus, sementara VLOOKUP standar hanya mencari berdasarkan satu nilai pada satu waktu.

Misalnya:

Provinsi + Kabupaten + Kecamatan

digabung menjadi:

Jawa BaratBandungCoblong

Nilai gabungan tersebut kemudian dapat digunakan sebagai lookup_value.

3. Mencari Data yang Sama dengan Fungsi VLOOKUP

Langkah pertama adalah membuat kolom bantuan pada tabel Tarif Ongkos Kirim.

Kolom bantuan sebaiknya diletakkan di bagian paling kiri tabel karena VLOOKUP melakukan pencarian pada kolom pertama table_array. Jika Provinsi berada di B3, Kabupaten di C3, dan Kecamatan di D3, gunakan rumus:

=B3&C3&D3

Operator & digunakan untuk menggabungkan beberapa nilai menjadi satu teks.

Selanjutnya, buat kolom bantuan pada Data Transaksi dengan cara yang sama. Setelah kedua kolom bantuan tersedia, kita dapat menggunakan hasil gabungan tersebut sebagai lookup_value. Misalnya formula yang digunakan adalah:

=VLOOKUP(G2;'Tarif Ongkos Kirim'!$A$3:$I$20;5;FALSE)

Mari kita uraikan:

• G2 → lookup_value, yaitu nilai gabungan dari data transaksi.

• 'Tarif Ongkos Kirim'!$A$3:$I$20 → table_array atau tabel referensi.

• 5 → data yang ingin diambil berada pada kolom kelima table_array.

• FALSE → pencarian harus menemukan data yang sama persis.

Mengapa menggunakan tanda $?

Tanda $ digunakan untuk membuat absolute reference. Dengan demikian, ketika formula disalin ke baris lain, range tabel referensi tidak ikut bergeser.

Contohnya:

A3:I20

dapat berubah ketika formula disalin.

Sementara:

$A$3:$I$20

akan tetap mengacu pada range yang sama.

Ini sangat penting ketika VLOOKUP digunakan untuk mengisi banyak baris sekaligus.

Baca Juga: Belajar Fungsi Tanggal & Waktu di Excel

4. Alternatif VLOOKUP dengan INDEX dan MATCH

VLOOKUP bukan satu-satunya cara untuk mengambil data dari tabel referensi. Alternatif yang cukup populer adalah kombinasi INDEX dan MATCH. Kombinasi ini dapat memberikan fleksibilitas lebih besar karena tidak bergantung pada posisi kolom pencarian seperti VLOOKUP. Pada contoh yang sama, formula yang dapat digunakan adalah:

=INDEX('Tarif Ongkos Kirim'!$E$3:$E$20;MATCH('Data Transaksi'!G2;'Tarif Ongkos Kirim'!$A$3:$A$20;0);1)

Formula tersebut menggabungkan dua fungsi:

• MATCH digunakan untuk menemukan posisi baris dari data yang dicari.

• INDEX digunakan untuk mengambil nilai dari posisi tersebut.

Pada fungsi MATCH, angka 0 digunakan untuk mencari kecocokan yang sama persis.

Dengan pendekatan ini, proses pencarian tidak harus bergantung pada posisi kolom seperti pada VLOOKUP.

Kalau kamu ingin memperdalam kemampuan Excel, kamu juga bisa mempraktikkan rumus-rumus tersebut menggunakan dataset sederhana dan memasukkannya ke dalam portofolio. Semakin sering digunakan dalam kasus nyata, semakin mudah memahami kapan dan bagaimana sebuah fungsi Excel sebaiknya digunakan.

FAQ

1. VLOOKUP digunakan untuk apa?

VLOOKUP digunakan untuk mencari suatu nilai pada kolom pertama tabel referensi, kemudian mengambil informasi dari kolom lain pada baris yang sama. Rumus ini banyak digunakan untuk mencocokkan data seperti ID, kode produk, harga, kategori, dan tarif.

2. Apa perbedaan VLOOKUP TRUE dan FALSE?

TRUE digunakan untuk pencarian nilai yang sama atau mendekati, sedangkan FALSE digunakan untuk mencari nilai yang sama persis. Untuk mencocokkan data seperti kode atau ID, FALSE biasanya menjadi pilihan yang tepat.

3. Mengapa VLOOKUP menghasilkan #N/A?

Error #N/A umumnya muncul karena nilai yang dicari tidak ditemukan pada kolom pertama tabel referensi. Periksa kembali lookup_value, data referensi, format data, serta kemungkinan adanya spasi atau perbedaan penulisan.

Yuk perdalam pemahaman excel kamu bersama DQLab! DQLab adalah platform edukasi pertama yang mengintegrasi fitur ChatGPT yang memudahkan beginner untuk mengakses informasi mengenai data science secara lebih mendalam.

DQLab juga menggunakan metode HERO yaitu Hands-On, Experiential Learning & Outcome-based, yang dirancang ramah untuk pemula. Jadi sangat cocok untuk kamu yang belum mengenal data science sama sekali. Untuk bisa merasakan pengalaman belajar yang praktis dan aplikatif, yuk sign up sekarang di DQLab.id atau ikuti Bootcamp Data Analyst with Excel berikut untuk informasi lebih lengkapnya atau ikuti Bootcamp Data Analyst with Excel!

Penulis: Reyvan Maulid