Rumus VLOOKUP Excel: Fungsi, Contoh, dan Cara Menggunakan

Pada bagian tutorial ini, kamu akan mempelajari cara menggunakan rumus VLOOKUP excel. Rumus VLOOKUP di excel cukup penting untuk dipahami karena fungsinya yang mampu menemukan data secara vertikal dalam suatu cell range. Pencarian data adalah salah satu proses penting jika kita ingin mengolah data di excel secara optimal.

Terus terang, tutorial VLOOKUP ini lumayan panjang jika kamu ingin mempelajari semua bagiannya. Hal ini karena kami membahas penggunaan VLOOKUP dari berbagai sisi sesuai yang sering kami dengar ditanyakan oleh pengguna excel seperti kamu!

Jika kamu ingin benar-benar menguasai rumus VLOOKUP, maka silahkan pelajari tutorial ini dari awal sampai habis. Tapi, jika belum punya waktu untuk itu, kamu bisa langsung pindah ke bagian pembahasan VLOOKUP yang kamu butuhkan lewat daftar isi. Silahkan bookmark tutorial ini kalau kamu ingin mempelajari bagian-bagian lainnya pada lain kesempatan!

Disclaimer: Artikel ini mungkin mengandung link afiliasi dari mana kami akan mendapatkan komisi untuk setiap transaksi/aksi terkualifikasi tanpa adanya biaya tambahan bagimu. Pelajari lebih lanjut

Ingin bekerja dengan lebih cepat dan mudah di Excel? Instal dan gunakan add-in Excel! Baca artikel ini untuk mengetahui add-in Excel terbaik yang bisa kamu gunakan menurut kami!

Fungsi VLOOKUP

Rumus VLOOKUP berguna untuk mencari data dari suatu cell range secara vertikal di excel.

Hasil Rumus VLOOKUP Excel

Hasil yang kamu dapatkan dari VLOOKUP adalah data hasil pencarian datamu.

Versi Excel Dari Mana VLOOKUP Mulai Bisa Digunakan

VLOOKUP mulai bisa digunakan sejak excel 2003.

Cara Penulisan dan Input

Secara umum, cara penulisan rumus VLOOKUP dapat diilustrasikan sebagai berikut:



Input yang kamu butuh masukkan dalam penulisannya tersebut dapat dijelaskan sebagai berikut:

• 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.

Contoh VLOOKUP 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.

Contoh VLOOKUP 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.

Jika kamu ingin menjadikan terbilang angka di atas sepuluh atau angka desimal, maka kamu harus memperbanyak isi tabel referensinya. Jika ingin cara lebih praktis untuk mendapatkan bentuk terbilang dari suatu angka, kamu bisa menggunakan rumus yang dibuat dari VBA. Petunjuk cara pembuatan dan pemakaian rumus tersebut bisa kamu lihat di tutorial ini.

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:



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)).

VLOOKUP Dengan Nilai Patokan Pencarian Sebagian

Jika data yang ingin dicari bentuknya hanya sebagian dari nilai patokan pencarianmu, kamu bisa memakai cara wildcard yang sudah dijabarkan sebelumnya. Namun, bagaimana jika kamu butuh sebagian saja dari nilai yang ada di suatu cell untuk dijadikan nilai patokan pencarian VLOOKUPmu? Untuk permasalahan tersebut, kamu perlu menggabungkan penulisan VLOOKUPmu dengan rumus LEFT, MID, atau RIGHT.

Penjelasan penggunaan dari masing-masing rumus tersebut, jika dibutuhkan, dapat kamu lihat di link yang diberikan tadi. Ketiga rumus tersebut dapat membantu kita menemukan bagian karakter dari suatu data yang kita miliki pada suatu cell.

Pemilihan rumus mana yang dipakai bersama VLOOKUP tentunya tergantung posisi bagian isi cell yang ingin kamu jadikan nilai patokan pencariannya. Pakai LEFT jika bagiannya berada di kiri, MID di tengah-tengah, dan RIGHT di kanan. Mereka akan dipakai sebagai pemisah bagian isi cell dari keseluruhan isi cellnya sebelum kemudian bagian tersebut dijadikan nilai patokan pencarian VLOOKUPmu.

Contoh penggunaan setiap dari ketiganya ketika digabungkan dengan VLOOKUP dapat dilihat di bawah ini.

VLOOKUP-LEFT:



VLOOKUP-MID:



VLOOKUP-RIGHT:



Terkadang, kode produk dalam suatu perusahaan memiliki bagian-bagian dari kodenya yang bisa diterjemahkan menjadi suatu informasi. Kombinasi LEFT, MID, atau RIGHT dengan VLOOKUP dapat membantu kita menerjemahkan informasi tersebut seperti dapat dilihat pada contohnya.

Dalam penulisan penggabungannya sendiri, kita menaruh LEFT/MID/RIGHT tersebut pada bagian input nilai patokan pencarian VLOOKUP kita. Tentukan berapa banyak karakter yang ingin kita ambil dari posisi sesuai dengan rumusnya. LEFT/MID/RIGHT akan menjalankan fungsinya untuk penggunaan VLOOKUP dengan nilai patokan pencarian sebagian saja.

Masing-masing dari ketiga rumus tersebut memiliki kegunaannya masing-masing tergantung dari posisi bagian data yang ingin kamu ambil. Silahkan pilih penggunaan rumusnya sesuai dengan kebutuhanmu!

VLOOKUP Kecocokan Ke-n (Bukan yang Pertama)

Secara proses, VLOOKUP akan otomatis mengambil kecocokan pertama dengan nilai patokan pencarian kita dari data-data pada kolom pertama cell rangenya. Bagaimana jika kita memerlukan kecocokan kedua atau ketiga? Untuk kebutuhan tersebut, kamu sebaiknya membuat kolom bantuan di sebelah kiri kolom pertama tersebut untuk membantumu.

Agar lebih jelasnya, kamu dapat melihat contoh berikut.



Kita membuat kolom “Helper” yang diisi data dari kolom tempat kita ingin mencari nilai patokan pencarian kita beserta dengan urutannya. Nantinya, input nilai patokan pencarian kita akan ditambah dengan urutan tersebut dan cell range VLOOKUP jadi mencakup juga kolom “Helper” tersebut. Kolom “Helper” akan menjadi kolom pertama cell range di mana nilai patokan pencariannya kemudian akan dicari.

Penginputan nilai patokan pencarian serta cell rangenya dapat kamu lihat pada formula bar contoh di atas. Mereka jadi sedikit dimodifikasi ketika kita ingin menemukan kecocokan ke-n dari nilai patokan pencarian VLOOKUP kita.

Untuk pengisian kolom “Helper” dengan tambahan nilai urutan datanya sendiri, kita dapat membuat rumus untuk membantu kita melakukannya dengan cepat. Contoh penulisan rumus tersebut dapat kamu lihat di bawah ini.



Kita menggunakan bantuan tanda & dan rumus COUNTIF untuk membantu kita menuliskan rumusnya. Tanda & digunakan untuk menggabungkan data kolom pertama dengan nomor urutannya. Sedangkan, COUNTIF digunakan untuk membuat nomor urutannya (jika kamu belum memahami penggunaan COUNTIF, silahkan pelajari tutorial berikut).

Bagaimana cara penulisan COUNTIF agar ia dapat menghasilkan nomor urutannya tersebut? Hal ini dapat kamu lihat pada formula bar screenshot contoh di atas.

Kita menginput cell range COUNTIF dari cell header kolom pertama sampai ke cell yang sejajar dengan cell kolom “Helper”nya. Kita buat koordinat cell header dari cell rangenya absolut agar ia tidak bergeser ketika kita salin rumus COUNTIFnya ke bawah nanti. Sifat absolut tersebut membuat jangkauan perhitungan COUNTIF selalu dari cell header sampai ke cell sejajar dengan tempat COUNTIFnya berada (sampai ke bawah manapun COUNTIFnya disalin).

Kriteria dari COUNTIF di sini adalah data kolom pertama yang ingin kita buatkan urutannya. Karena cell rangenya kita atur seperti yang dijelaskan sebelumnya, hal ini membuat COUNTIF jadi bisa memberikan urutan datanya.

Jika dari cell header sampai ke cell sejajar kolom pertamanya tersebut sudah terdapat dua data yang sama, COUNTIF akan menghasilkan 2. Jika tiga, maka COUNTIF akan menghasilkan 3. Hal ini membuat rumus COUNTIF menjadi rumus yang pas untuk mendapatkan angka urutan datanya.

Kita akan mengisi kolom “Helper” tersebut dengan data kolom pertama dan nomor urutannya. Pada penulisan VLOOKUPnya, kita kemudian memakai kolom “Helper” tersebut untuk mencari nilai patokan pencarian ke-n dengan n sesuai keinginan kita.

VLOOKUP Dengan 2 Kriteria/Nilai Pencarian Atau Lebih

Bagaimana jika kita ingin menggunakan VLOOKUP tapi terdapat dua kolom atau lebih memuat nilai patokan pencarian yang ingin kita gunakan? Sama seperti VLOOKUP untuk kecocokan ke-n tadi, kita perlu membuat kolom “Helper” dan menggabungkan data kolom-kolom tersebut ke dalamnya. Contohnya dapat kita lihat di bawah.



Kunci dari penggunaan VLOOKUP dengan dua atau lebih kriteria/nilai pencarian adalah menggabungkan semua kriteria/nilai pencarian tersebut dalam satu penulisan. Hal ini perlu dilakukan baik bagi kolom pertama cell range VLOOKUP maupun bagi nilai patokan pencariannya.

Pada contohnya, kita perlu mencari kuantitas penjualan apel di wilayah penjualan B. Karena terdapat dua kriteria pencarian di sana, apel dan wilayah B, maka kita perlu menggabungkannya dalam input nilai pencariannya. Seperti terlihat di formula barnya, kita jadikan input nilai patokan pencarian VLOOKUPnya menjadi “ApelB” untuk mengakomodasi kebutuhan tersebut.

Pada cell range VLOOKUPnya, kita tambahkan kolom “Helper” untuk menjadi kolom pertama/paling kirinya. Isi dari setiap cell kolom “Helper” tersebut adalah penggabungan penulisan data kolom “Nama Barang” dan “Wilayah Penjualan”. Hal ini karena data di kedua kolom inilah yang ingin kita jadikan kriteria pencariannya



Bagaimana cara menggabungkan data dari kedua kolom tersebut? Caranya dapat kamu lihat di formula bar screenshot di atas.

Gunakan tanda & di antara koordinat cell kolom tempat data yang ingin dijadikan referensi pencariannya yang sejajar dengan cell kolom “Helper”nya. Tinggal kamu salin rumus penggabungan data dengan tanda & tersebut untuk mendapatkan semua hasil penggabungan yang kamu butuhkan.

Setelah itu, kamu tinggal menginput nilai patokan pencarian dan kolom “Helper” tersebut ke rumus VLOOKUPmu. Kamu akan mendapatkan hasil pencarian data dengan 2 kriteria/nilai pencarian!

Untuk lebih dari 2 kriteria/nilai pencarian, kamu tinggal menggabungkan lebih banyak lagi di nilai patokan pencarian dan kolom “Helper”mu. Pastikan kolom “Helper”nya selalu berada di sebelah paling kiri dari input cell range di VLOOKUPmu agar pencarian datanya berjalan lancar.

VLOOKUP Dengan Tabel Referensi Dinamis

Dalam penggunaan VLOOKUP, mungkin kamu juga ingin menentukan cell range tabel referensimu berdasarkan nilai suatu cell tertentu. Nilai A berarti menggunakan tabel referensi 1, nilai B berarti menggunakan tabel referensi 2, dan seterusnya.



Bagaimana cara melakukannya? Kombinasi rumus IF dan VLOOKUP adalah jawabannya.



Pada screenshot di atas, terlihat bagaimana hasil pencarian datanya jika bulan yang diminta adalah Juni. Jika bulan yang diminta Juli, hasilnya akan merujuk ke tabel penjualan Juli seperti yang dapat dilihat pada screenshot berikut.



Bagaimana cara menuliskan rumus VLOOKUP agar dapat berpindah tabel referensi sesuai isi suatu cell tersebut? Rumus IF memegang peranan penting di sini dengan penggunaannya pada bagian input cell range dari VLOOKUP kita.

Masukkan kondisi logika rumus IFnya tergantung dari isi cell yang ingin kita referensikan. Kemudian jika TRUE, masukkan cell range tabel referensi sesuai keinginan kita dan jika FALSE, masukkan cell range tabel referensi lainnya.

Untuk lebih dari 2 tabel referensi, kamu tinggal memasukkan IF bertingkat pada bagian input cell range VLOOKUP tersebut. Masukkan IF sebanyak yang kamu perlukan sesuai dengan jumlah cell range tabel referensi yang kamu inginkan untuk VLOOKUPnya.

VLOOKUP Dengan Kolom Hasil Dinamis

Selain tabel referensi dinamis, kamu juga mungkin ingin menentukan urutan kolom tempat kamu mengambil hasil VLOOKUPmu berdasarkan isi cell tertentu. Untuk melakukannya, kamu memerlukan kombinasi rumus IF dan VLOOKUP juga. Hanya saja, untuk kolom hasil dinamis, kamu menaruh rumus IFnya pada bagian input urutan kolom hasil dari VLOOKUPmu.



Contoh di atas memperlihatkan bagaimana penggunaan IF bersama dengan VLOOKUP untuk membuat kolom hasilnya menjadi dinamis. Jika cell bulannya diganti menjadi Juli, maka VLOOKUP akan mendapatkan hasil pencarian datanya di kolom Juli.



Perilaku VLOOKUP tersebut bisa didapatkan dengan penulisan rumus IF secara benar di bagian input urutan kolom hasilnya. Taruh nilai cell penentu kolom hasil sebagai kondisi logika IFnya. Lalu, masukkan urutan kolom hasilnya jika nilai cellnya sesuai dengan kondisi logika yang kamu buat. Masukkan juga urutan kolom hasilnya jika tidak sesuai.

Jika terdapat lebih dari dua kolom yang ingin kamu jadikan pilihan dalam VLOOKUPnya, gunakan implementasi rumus IF bertingkat pada VLOOKUPmu. Masukkan jumlah rumus IF sesuai dengan jumlah kolom hasil yang kamu inginkan untuk VLOOKUPmu.

Mencari Data Secara Horizontal: HLOOKUP

Seperti yang sudah dijelaskan di bagian sebelumnya, VLOOKUP dapat kamu gunakan jika kamu ingin mencari data secara vertikal. Ia mendasarkan proses pencariannya pada kolom-kolom dalam suatu cell range. Selain itu, ia juga mendapatkan hasilnya dari penemuan baris data yang sesuai dengan nilai patokan pencariannya.

Bagaimana jika kita ingin mencari data secara horizontal, berkebalikan dari arah proses yang dilakukan oleh VLOOKUP? Salah satu caranya adalah dengan memanfaatkan rumus saudara dari VLOOKUP yaitu HLOOKUP.

Sesuai huruf depan nama formulanya, HLOOKUP akan mencari suatu data secara horizontal dalam cell rangemu. Ia melakukan pencariannya berdasarkan baris dan mendapatkan hasilnya dari penemuan kolom data yang cocok dengan nilai patokan pencariannya.

Secara umum, penulisan rumus HLOOKUP dapat dijabarkan sebagai berikut:



Sekilas, input dari HLOOKUP hampir sama dengan VLOOKUP. Hanya saja, HLOOKUP membutuhkan urutan baris hasil, bukan kolom hasil, pada inputnya. Selain itu, ia akan mencari nilai patokan pencariannya pada baris pertama (bukan kolom pertama seperti VLOOKUP) dari cell rangenya.

Sifat pencarian TRUE/FALSE dari HLOOKUP sendiri sama seperti VLOOKUP. Jangan lupa mengurutkan baris pertamamu dari kecil ke besar jika menggunakan input TRUE di sini.

Berikut contoh penggunaan dari HLOOKUP pada excel.



Dari sana, terlihat bagaimana penulisan HLOOKUP dilakukan dan bagaimana ia bekerja. Pada contoh tersebut, HLOOKUP mencari angka lebih kecil terdekat dari 82 di baris pertama cell rangenya (karena input sifat pencariannya adalah TRUE dan tidak ada 82 di baris pertamanya). Ketika menemukannya (60), ia akan melihat posisi kolomnya sebelum mengambil data di baris hasilnya untuk mendapatkan hasil.

Jika kamu ingin mempelajari lebih lanjut mengenai HLOOKUP, silahkan lihat tutorial spesifiknya di sini.

Alternatif VLOOKUP: INDEX MATCH

VLOOKUP bisa disebut lumayan kaku dalam implementasinya. Hal ini karena ia hanya terbatas mencari data secara vertikal dan hanya bisa memproses pencarian ke kanan. Selain itu, nilai patokan pencariannya hanya bisa dicari di kolom pertama dari input cell rangenya.

Ingin rumus yang lebih fleksibel dalam kesemua hal tersebut? Gunakan kombinasi INDEX MATCH.

Secara terpisah, rumus INDEX adalah rumus yang mampu mengambil data berdasarkan input cell range, posisi kolom, dan posisi barisnya. Di lain pihak, MATCH adalah rumus yang mampu memberikan hasil posisi dari suatu data dalam suatu cell range baris/kolom.

Digunakan bersamaan, mereka dapat menjadi suatu rumus pencarian data yang sangat ampuh. Bahkan, menurut kami, INDEX MATCH lebih bagus daripada VLOOKUP jika kamu sudah paham cara pemakaiannya.

Contoh dari penggunaan INDEX MATCH dapat kamu lihat di bawah ini.



Dari contoh tersebut, kamu bisa melihat penulisan dan hasil INDEX MATCH di excel. Secara umum, kamu perlu memasukkan MATCH pada rumus INDEXnya di bagian input tempat ia perlu mencari datamu. Masukkan MATCH di input posisi baris INDEX jika data yang kamu cari perlu dicari secara baris per baris. Di lain pihak, masukkan MATCH di input kolom INDEX jika data yang kamu cari perlu dicari secara kolom per kolom.

Ingin mempelajari kombinasi INDEX MATCH secara lebih mendalam? Pelajari ia di tutorial Compute Expert berikut.

Latihan

Setelah mempelajari bagaimana cara menggunakan rumus VLOOKUP excel secara lengkap, sekarang saatnya mengerjakan latihan. Hal ini agar kamu bisa semakin memperdalam pemahamanmu tentang cara menggunakan rumus VLOOKUP.

Unduh file latihannya dan jawab semua pertanyaannya. Unduh file kunci jawabannya jika kamu sudah menjawab semua pertanyaannya dan ingin mengecek jawabanmu. Atau mungkin ketika kamu bingung bagaimana cara menggunakan fungsi VLOOKUP untuk menjawab pertanyaannya!

Link file latihan:
Unduh di sini

Pertanyaan

Jawab setiap pertanyaan di cell berwarna abu-abu yang sesuai nomor pertanyaannya dengan menggunakan VLOOKUP!

1. Berapa nilai penjualan wilayah E berdasarkan tabel referensinya?
2. Berapa kuantitas penjualan wilayah M berdasarkan tabel referensinya?
3. Berapa tenaga penjual dari wilayah penjualan dengan kuantitas penjualan lebih kecil serta mendekati 2000?

Link file kunci jawaban:
Unduh di sini

Catatan Tambahan

Mungkin kamu sudah menyadarinya bahwa kamu tidak perlu memasukkan header tabelmu dalam input cell range tabel referensi VLOOKUPmu. Yang terpenting adalah tempat di mana kamu benar-benar mencari datanya sudah masuk.



Tutorial terkait untuk kamu pelajari juga: