Mencari Data dengan Mudah: Cara Efektif Menggunakan VLOOKUP di Excel!

 

Hai Sobat Guild! 

Pada laman ini, Guild Seven akan memperkenalkan materi baru nih!

Apa itu VLOOKUP?

Vlookup (V singkatan dari 'Vertikal') adalah fungsi bawaan di excel yang memungkinkan membangun hubungan antara kolom-kolom excel yang berbeda. Dengan kata lain, ini memungkinkan Anda menemukan (mencari) nilai dari satu kolom data dan mengembalikan nilainya masing-masing atau nilai yang sesuai dari kolom lain. Rumus VLOOKUP berguna untuk mencari data dari suatu cell range secara vertical di excel.

Fungsi VLOOKUP

Rumusnya:

Fungsi VLOOKUP  = VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup] )

Keterangan :

·       lookup_value = nilai patokan pencarian untuk dicari pada kolom pertama dari cell range tabel referensimu

·       table_array = koordinat cell range tabel referensimu

·       col_index_num = urutan kolom (dihitung dari kiri ke kanan) tabel referensi tempat hasil dari rumus ini akan diambil

·       [range_lookup] = bersifat opsional. Menentukan apakah nilai patokannya akan dicari sama persis atau boleh kisarannya saja. Input TRUE untuk kisaran atau FALSE untuk sama persis

 

Contoh VLOOKUP

 1. Pencarian Biodata

Berikut akan diberikan dan dijelaskan contoh pertama yang memperlihatkan kegunaan VLOOKUP beserta penulisan dan hasilnya.

Dari sini, terlihat jelas bagaimana VLOOKUP digunakan untuk mencari data secara vertikal. Cara menggunakan rumus VLOOKUP excel sendiri adalah dengan memasukkan input seperti yang sudah dijabarkan pada bagian sebelumnya dengan benar. Input tersebut adalah nilai patokan pencariannya, cell range tempat pencariannya dilakukan, urutan kolom tempat hasilnya diambil, serta sifat pencariannya.

Cara kerja rumus ini sendiri adalah sebagai berikut: ia akan melihat kolom pertama cell rangemu dan mencari nilai patokannya di sana. Ketika ditemukan, ia akan mengambil posisi barisnya untuk kemudian dipakai untuk mengambil hasil pada kolom hasilnya. Jika terdapat lebih dari satu data pada kolom pertama yang cocok dengan nilai pencarianmu, akan diambil yang posisi barisnya paling atas.

Input terakhirnya sendiri yang bersifat opsional, sifat pencariannya, juga penting untuk dipahami. Kamu dapat memasukkan TRUE/FALSE di sini dengan asumsinya adalah TRUE jika kamu tidak memasukkan input apapun. Jika TRUE, berarti jika data sama persis tidak ditemukan pada kolom pertama, ia akan mencari nilai terkecil terdekat dengan nilai pencariannya. Jika inputnya adalah FALSE, jika data sama persis tidak ditemukan, maka VLOOKUP akan menghasilkan error.

Penting diingat bahwa jika inputmu TRUE di sini, kamu harus mengurutkan data pada kolom pertama cell rangenya dari kecil ke besar. Jika tidak, maka hasil VLOOKUPmu bisa menjadi salah atau bahkan error.

Praktek dari semua penjelasan ini dapat dilihat pada contoh cara menggunakan rumus VLOOKUP excel di atas. Dalam contohnya tersebut, terlihat nama yang dicari di sana adalah “Moka”. Dari situ, rumus ini mencari “Moka” pada kolom pertama input cell rangenya dan menarik data berdasarkan input urutan kolom hasilnya. Pada input urutan kolom hasil tersebut, dimasukkan angka 2 dan 3 sesuai urutan dari kolom “Umur” dan “Pekerjaan” dalam cell rangenya.

Kemudian, karena input terakhirnya adalah FALSE untuk kedua rumusnya, maka data pada kolom pertama tidak perlu diurutkan. Kombinasi semua input tersebut memberikan hasil yang sesuai dengan kebutuhan pencarian data pada contohnya.

2. Rentang Nilai dan Terjemahan Hurufnya

Contoh berikutnya yang dapat kita lihat untuk memperjelas pemahaman kita mengenai VLOOKUP adalah penentuan huruf nilai dari suatu rentangan angkanya.

Pada contoh tersebut, dapat kita lihat bagaimana rumus VLOOKUP memberikan hasil jika sifat pencariannya adalah TRUE atau kisaran. Ketika sedang ingin mengubah suatu angka nilai menjadi huruf, memang biasanya rentang nilailah yang menjadi patokannya.

Dalam contoh tersebut, terlihat bagaimana VLOOKUP mengkonversikan nilai 72 dari Jenny berdasarkan tabel referensi di sebelah kiri. Seperti dijelaskan sebelumnya, VLOOKUP otomatis mengasumsikan input bagian terakhirnya sebagai TRUE jika tidak diberikan input apapun di sana. Oleh karena itu, penulisan rumus VLOOKUP pada contohnya tidak perlu menginput apapun pada bagian tersebut (jika kita input TRUE di sana, maka hasil rumusnya akan sama persis).

VLOOKUP dengan TRUE akan memprioritaskan nilai sama persis terlebih dahulu sebelum mencari nilai lebih kecil terdekat. Seperti bisa dilihat di tabel referensinya, tidak terdapat nilai 72 di sana.

Karena itu, VLOOKUP mengambil posisi baris nilai kecil terdekatnya yaitu 65. Karena pada kolom kedua tabel referensinya (sesuai input permintaan urutan kolom di penulisan rumus VLOOKUPnya) yang sejajar dengan 65 adalah huruf C, maka huruf itulah yang menjadi hasil dari VLOOKUPnya.

Patut diperhatikan juga bahwa kolom pertama tabel referensi contohnya tersebut sudah diurutkan dari kecil ke besar. Hal ini, sekali lagi, penting dilakukan ketika kita menggunakan sifat pencarian kisaran dari VLOOKUP. Jika tidak diurutkan, maka kemungkinan besar hasil VLOOKUPnya salah atau error, seperti dapat dilihat di screenshot berikut.

3. Rumus Terbilang Manual

Contoh berikutnya merupakan salah satu aplikasi penggunaan VLOOKUP yang bisa kamu lakukan di excelmu untuk merubah angka menjadi bentuk terbilangnya.

Pada contoh tersebut, terlihat bagaimana VLOOKUP dapat menjadi rumus terbilang dalam excel. Asalkan ada tabel referensinya, ia dapat merubah suatu angka menjadi bentuk hurufnya. Pastikan input sifat pencariannya adalah FALSE agar ia tidak salah menarik bentuk huruf dari tabel referensinya.

Langkah-langkah Penulisan

Berikut akan dijelaskan cara menggunakan rumus VLOOKUP excel secara langkah per langkah. Pengunaan VLOOKUP di excel sendiri cukup rumit jika kamu belum pernah belajar VLOOKUP. Oleh karena itu, contoh penulisan akan diberikan juga dalam bentuk screenshot pada setiap langkah penjelasannya agar semakin mempermudah proses pemahamanmu.

1. Ketik tanda sama dengan ( = ) di cell tempat kamu ingin menaruh hasilnya

2. Ketik VLOOKUP (boleh dengan huruf besar atau huruf kecil) dan tanda buka kurung setelah =

3. Masukkan nilai patokan pencarian atau koordinat cell tempat nilai pencarian berada untuk nanti dicari dalam cell range tabel referensi. Lalu masukkan tanda koma ( , )

4. Drag kursor pada cell range tempat tabel referensimu berada lalu masukkan tanda koma. Nantinya, nilai patokan pencarianmu akan dicari di kolom pertama dari tabel referensi ini

5. Masukkan urutan kolom tempat di mana hasil pencarianmu akan didapatkan dari tabel referensimu. Hasil pencariannya akan didapatkan dari baris di mana nilai patokan pencarianmu ditemukan di kolom pertama dan juga kolom ini. Jika terdapat lebih dari satu data ditemukan cocok dengan nilai patokan pencariannya pada kolom pertama, maka baris teratas yang akan diambil

6. Opsional: Untuk menentukan apakah sifat pencariannya harus dicari sama persis atau kisarannya saja untuk nilai patokan pencariannya, lakukan ini. Masukkan tanda koma dan ketikkan TRUE/FALSE (boleh dengan huruf besar atau huruf kecil).

TRUE untuk kisaran dan FALSE untuk sama persis. Maksudnya kisaran adalah jika tidak ada kesamaan antara semua data pada kolom pertama tabel referensi dengan nilai pencarian, maka dilakukan ini. Fungsi rumus VLOOKUP akan menjadikan nilai lebih kecil terdekat dengan nilai pencarian yang ditemukan pada kolom pertama sebagai referensinya.

Sebagai catatan penting, kamu harus mengurutkan nilai kolom pertamanya dari kecil ke besar agar mode TRUEnya ini berfungsi dengan benar. Jika tidak ada input dalam bagian ini, maka VLOOKUP akan menganggap inputnya sebagai TRUE

7. Ketik tanda tutup kurung

8. Tekan tombol Enter

9. Prosesnya selesai!

Penjelasan mengenai TRUE/FALSE Dalam Fungsi VLOOKUP (Sifat/Mode Pencariannya)

Input TRUE atau FALSE layak mendapatkan perhatian tersendiri dalam penulisan rumus VLOOKUP karena ia krusial dalam menentukan hasil pencarian datamu. Seperti sudah disebutkan sebelumnya, masukkan TRUE jika VLOOKUP boleh mencari kisaran nilai patokan pencariannya saja dan FALSE jika mesti sama persis.

Jika kamu memasukkan mode pencarian TRUE, maka VLOOKUP boleh mengambil nilai lebih kecil terbesar dari nilai patokan pencariannya sebagai hasil temuannya. Jika FALSE, maka jika VLOOKUP tidak menemukan nilai sama persis dengan nilai patokan pencariannya, maka hasilnya akan menjadi error.
Jika kamu tidak memasukkan apapun pada bagian input ini, maka VLOOKUP akan mengganggap inputmu adalah TRUE atau boleh kisaran saja.
Gunakan input TRUE/FALSE tersebut sesuai dengan kebutuhanmu. Yang harus kamu ingat adalah jika kamu menggunakan TRUE, kolom pertama tempat pencarian nilai patokanmu harus diurutkan dari kecil ke besar. Jika tidak, maka hasil pencarian VLOOKUP akan menjadi salah atau error.
Error dari TRUE akibat belum diurutkannya kolom pertama ini juga jadi alasan seringnya VLOOKUP terkadang tidak bekerja dengan baik dalam excelmu.

Hal-hal yang Dapat Menyebabkan VLOOKUP Error

Pemakaian VLOOKUP adakalanya tidak menghasilkan hasil pencarian data yang sesuai dengan keinginan kita atau malah menghasilkan error. Faktor-faktor penyebab kejadian tersebut tentunya harus menjadi perhatikan kita agar kita tidak melakukan kesalahan penggunaan VLOOKUPnya.

Dari berbagai alasan yang bisa menjadi penyebab errornya VLOOKUP, berikut 3 yang mungkin palling sering dijumpai:

·       Memakai TRUE sebagai input mode/sifat pencariannya tapi data pada kolom pertamanya belum diurutkan dari kecil ke besar

·       Nilai patokan pencarian datanya tidak ditemukan dalam kolom pertama cell range tabel referensinya

·       Kesalahan input di bagian cell range tabel referensi dan/atau urutan kolom tempat pengambilan isi rumusnya

Perhatikan penggunaan VLOOKUPmu agar tidak melakukan penyebab-penyebab error seperti yang dijabarkan di atas.

Antisipasi Error VLOOKUP: IFERROR VLOOKUP

Mungkin kita sudah berusaha sebisa mungkin untuk menghindari munculnya error dari penggunaan VLOOKUP kita. Namun, adakalanya error tetap ditemukan dari rumusnya. Hal tersebut bisa membuat error juga pengolahan data kita yang memakai hasil VLOOKUP tersebut sebagai referensinya.

Apa yang bisa kita lakukan untuk mengantisipasi error tersebut dan membuatnya tidak mengganggu proses pengolahan data kita? Jawabannya adalah dengan mengkombinasikan penulisan VLOOKUP kita dengan IFERROR.

Secara umum, penulisan penggabungan rumus IFERROR dan VLOOKUP adalah sebagai berikut:

=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), value_if_error)

Penjelasan input pada penulisan VLOOKUPnya di atas sama seperti yang sudah dijelaskan pada bagian sebelumnya. Sedangkan pada IFERROR, value_if_error adalah nilai yang ingin kita hasilkan dari rumusnya jika VLOOKUP kita memang menghasilkan error.

Contoh dari penggunaan IFERROR VLOOKUP di excel sendiri adalah sebagai berikut:

Pada contoh tersebut, terlihat bagaimana kombinasi IFERROR VLOOKUP dituliskan. Dengan adanya IFERROR, maka error VLOOKUP diubah menjadi tulisan “Tidak Ditemukan” di sana. Jika tidak ada IFERROR, maka VLOOKUP akan menghasilkan error #N/A.

Nilai alternatif jika error dihasilkan oleh VLOOKUP dalam input IFERROR sendiri biasanya berupa kata-kata seperti “Tidak Ditemukan” atau data kosong (“”). Namun, tentunya terserah kamu nanti mau menggunakan nilai alternatif apa jika menggunakan kombinasi rumus ini.

VLOOKUP Dengan Karakter Wildcard

Dalam input nilai patokan pencarian dari VLOOKUP, kita juga dapat gunakan karakter wildcard. Bagi yang tidak tahu karakter wildcard itu apa, ini adalah karakter yang dapat diganti karakter apa saja dalam penulisan suatu data.

Dalam excel, terdapat dua karakter berjenis wildcard yang dapat digunakan:

·       Tanda bintang (*): mewakili karakter apa saja dengan jumlah berapa saja/tidak terbatas

·       Tanda tanya (?): mewakili karakter apa saja dengan jumlah satu

Jika mereka digunakan di VLOOKUP, karakter wildcard biasanya ditempatkan di bagian input nilai patokan pencarian. Contoh penerapannya dalam VLOOKUP dapat dilihat di screenshot berikut:

Pada kedua contohnya, dapat dilihat efek pemakaian tanda * dan ? pada nilai patokan pencarian VLOOKUP. Tanda * pada “S*” membuat VLOOKUP mencari data dengan awalan “S”. Sedangkan tanda ? pada “Mang?a” membuat VLOOKUP mencari data dengan awalan “Mang” dan akhiran “a”.

Dalam contohnya, juga terlihat bahwa tanda * dapat diisi dengan karakter apa saja dengan jumlah berapa saja (“S*” bisa berarti “Semangka”, “Sirsak”, atau kata lainnya asalkan berawalan S). Tanda ?, di lain pihak, hanya melambangkan satu karakter saja yang bebas (“Mang?a” bisa berarti “Mangga” atau “Mangua” tapi tidak bisa “Manggggga” atau “Mangjasa”).

Penempatan tanda wildcard * dan ? sendiri boleh di bagian mana saja dari nilai patokan pencarian yang kamu masukkan di VLOOKUP. Jika kamu sebenarnya ingin menggunakan tanda * dan ? dalam nilai patokan pencarianmu, maka gunakan tanda ~ di depan tandanya (contoh: input nilai patokan pencarian “Susah~?” akan membuat VLOOKUP mencari data teks “Susah?” bukan “Susaha” atau “Susahe”).

VLOOKUP Dengan Proses Pencarian ke Kiri

Pada dasarnya, proses pencarian data VLOOKUP selalu mengarah ke kanan. Ia mencari nilai patokan pencarianmu pada kolom paling kiri cell rangenya sebelum menemukan hasil pencariannya di sebelah kanan dari kolom tersebut.

Bagaimana jika nilai patokan pencariannya ada di sebelah kanan dari kolom tempat kita ingin mendapatkan hasil pencarian data kita? Solusinya adalah dengan tidak menggunakan VLOOKUP untuk melakukan proses pencarian datamu, tapi dengan INDEX MATCH.

INDEX MATCH adalah kombinasi rumus INDEX dan MATCH di excel yang dapat digunakan untuk proses pencarian data yang lebih fleksibel. Jika kamu ingin mempelajari rumus ini secara lebih mendalam, silahkan ikuti tutorialnya dari Compute Expert di sini.

Berikut contoh dari penggunaan INDEX MATCH untuk mencari data dengan proses pencarian ke kiri.

Seperti bisa dilihat pada contohnya, INDEX MATCH bisa digunakan untuk proses pencarian data yang mengarah ke kiri. Hal ini karena sifat input cell range dan nilai patokan pencarian INDEX MATCH yang lebih fleksibel. Cell range INDEX dan MATCH bisa ditempatkan di mana saja dengan nilai patokan pencariannya dimasukkan ke dalam rumus MATCH.

Hal ini tentunya berbeda dari batasan VLOOKUP yang mengharuskan cell range nilai patokan pencarian dan hasil pencariannya dalam satu lingkup. Selain itu, nilai patokannya juga harus dicari pada kolom paling kiri dari cell range tersebut. Hal ini menyebabkan proses pencarian VLOOKUP jadi terbatas bersifat ke kanan saja arahnya.

Jadi, jika kamu memang membutuhkan proses pencarian ke kiri, gunakan INDEX MATCH untuk mendapatkan datamu.

VLOOKUP Dengan Tabel Referensi Berbeda Sheet

Salah satu pertanyaan yang sering ditanyakan ketika menggunakan VLOOKUP adalah: Bagaimana cara menuliskan VLOOKUP jika tabel referensinya berada di sheet berbeda?

Sebenarnya, cara penulisannya sangat mirip dengan VLOOKUP yang tabel referensinya berada di sheet yang sama. Hanya saja, ketika menginput cell range tempat pencarian datanya, klik sheet tempat tabel referensinya berada dahulu.

Kemudian, drag kursor cellmu pada cell range tabel referensinya tersebut sebelum pindah lagi ke cell tempat penulisan VLOOKUPmu. Dari sana, kamu tinggal lanjutkan penulisan rumus VLOOKUPmu seperti biasa.

Untuk lebih jelasnya, mungkin ada baiknya kamu melihat screenshot di bawah ini. Pada screenshot tersebut, terlihat tabel referensinya berada di sheet bernama “Sheet1”

Kita ingin menuliskan rumus VLOOKUPnya di sheet bernama “Sheet2” yang berbeda dengan sheet tabel referensinya tadi. Bagaimana cara menuliskannya? Berikut contoh penulisannya dengan kondisi tersebut.

Seperti dapat dilihat pada formula barnya di atas, pada input cell rangenya, tulisannya adalah Sheet1!B5:C9. Penulisan seperti itu akan otomatis kamu dapatkan jika kamu pindah ke “Sheet1” dan drag kursormu di cell B5 sampai C9.

Atau jika kamu ingin mengetikkan input cell rangenya secara langsung, jangan lupa menuliskannya dengan format seperti itu (Nama_Sheet!Cell_Range). Penulisan dengan format tersebut untuk referensi cell range beda sheet penting agar VLOOKUPmu dapat berfungsi dengan baik.

Setelah memasukkan cell range tempat pencarian datanya, kamu tinggal memasukkan input lainnya dengan cara seperti biasa (seperti jika tabel referensinya ada di sheet yang sama dengan tempat penulisan VLOOKUPmu).

VLOOKUP Dengan Tabel Referensi Berbeda File

Bagaimana jika tabel referensi yang ingin kamu gunakan ada di file yang berbeda? Jawabannya adalah kamu harus memasukkan cell range tabel referensimu dengan memperhitungkan nama file excel sekaligus nama sheet tempatnya berada.

Perhatikan contoh berikut. Misalkan, tabel referensi yang ingin digunakan oleh VLOOKUPmu ada di file dengan nama “Book2.xlsx” (xlsx adalah ekstensi yang digunakan oleh file excel ini). File tersebut berbeda dengan tempat di mana penulisan VLOOKUPnya dilakukan.

Bagaimana cara mereferensikannya tabel referensi tersebut? Tuliskan nama file excelnya serta nama sheetnya sebelum cell range tabel referensi tersebut pada input VLOOKUPmu!

Penulisannya berformat seperti ini jika file tempat tabel referensinya berada juga sedang kamu buka: [Nama_Workbook]Nama_Sheet!Cell_Range. Sebagai contoh, lihat screenshot di bawah untuk penulisan VLOOKUP di file berbeda dengan tabel referensi di “Book2.xlsx” tadi.

Setelah membuka kedua file excelnya (tempat VLOOKUPmu berada dan tempat tabel referensinya berada), masuk ke file tempat tabel referensinya ketika kamu sedang memasukkan bagian input tabel referensi di VLOOKUPmu.

Kemudian, drag kursormu pada cell range tempat data tabelnya berada. Lalu, ketik koma dan masuk lagi ke file VLOOKUPmu. Setelah itu, kamu tinggal memasukkan input lainnya seperti biasa.

Jika kamu ingin menuliskan cell rangenya secara langsung, maka penulisannya adalah seperti ini: [Book2.xlsx]Sheet1!B5:C9. Lakukan penulisan tersebut ketika file tempat tabel referensimu sedang dibuka. Jika file tersebut sedang ditutup, maka kamu harus mengetikkan file pathnya juga. (Contoh: ‘C:\Documents\[Book2.xlsx]Sheet1’!B5:C9 (Windows) atau ‘/Users/Documents/[Book2.xlsx]Sheet1’!B5:C9 (Mac)).