Rumus excel Vlookup merupakan satu dari sekian banyak rumus di Microsoft Excel yang paling sering digunakan untuk membantu pekerjaan yang berkaitan dengan data.
Rumus Excel Vlookup sendiri termasuk dalam kategori lookup and reference function atau fungsi yang berhubungan dengan pencarian dan referensi.
Kata Vlookup sendiri merupkan gabungan antara “V” dan “lookup” dimana huruf “V” tersebut bermakna vertikal atau lurus.
Fungsi Vlookup bertujuan untuk mencari nilai tertentu yang mana bersumber dari tabel utama atau tabel referensi. Perlu diketahui bahwa tabel referensi dan tabel yang digunakan untuk mengaplikasikan Vlookup berada di posisi yang berbeda.
Sebagaimana rumus excel lainnya, pencarian nilai Vlookup juga dilakukan dengan cara mengetikkan kata kunci tertentu pada sel atau formula bar.
Banyak yang beranggapan bahwa fungsi rumus excel Vlookup itu sulit. Namun, asumsi ini dapat ditepis manakala mampu memahami dengan benar komponen penyusun dari sistaksisnya.
Pada artikel inilah Anda akan dijelaskan secara gamblang tentang seluk beluk Vlookup pada excel. Setiap ulasannya juga akan dilengkapi dengan sebuah ilustrasi yang mudah dipahami.
Komponen Penyusun dari Rumus Excel Vlookup Beserta Contohnya
Sebelum memulai untuk mengaplikasikan Vlookup, Anda harus mengetahui dahulu ciri khas tabel data yang akan digunakan. Silahkan baca dulu artikel sebelumnya disini. Yaitu tentang berbagai rumus microsoft excel sebagai pengantar.
Pernyataan ini bukanlah tanpa alasan karena rumus excel tersebut ternyata hanya berlaku ketika data referensi berbentuk vertikal. Artinya, deretan judul tabel berada pada posisi atas, sedangkan file data-datanya tersusun secara vertikal ke bawah.
Hal lain yang harus dipelajari terlebih dahulu adalah tentang cara penulisan rumus atau bentuk sintaksisnya. Selain itu, pahami dengan benar setiap komponen penyusunnya agar mudah dalam mengaplikasikan rumus excel Vlookup pada data.
Adapun bentuk sintaksis dari rumus Vlookup pada Ms. Excel adalah:
=VLOOKUP(Nilai yang dicari; Tabel referensi; Nomor kolom; Range lookup)
Terlihat jelas pada sintaksis di atas bahwa rumus Vlookup terdiri dari 4 komponen utama. Adapun penjelasan lengkapnya dari masing-masing komponen tersebut antara lain:
Nilai yang dicari
Nilai yang dicari merupakan sebuah nilai atau data yang dicari. Adapun posisi sel dari nilai yang dicari tersebut harus berada di kolom pertama atau kolom paling kiri dari tabel referensi.
Argumen atau nilai yang digunakan dapat berupa angka, teks, tanggal, nilai logika, maupun hasil referensi dari sel lain. Jika nilai yang dicari berupa teks, perbedaan penulisan antara huruf besar dan kecil tidak akan berpengaruh.
Tabel referensi
Tabel referensi merupakan tabel utama yang dijadikan sebagai acuan dalam pengambilan data. Dalam beberapa kasus, rumus Vlookup exceldapat diaplikasikan untuk pencarian nilai pada 2 tabel referensi sekaligus. Pembahasan tersebut akan diuraikan secara lengkap pada materi selanjutnya.
Kolom pertama dari tabel yang dijadikan referensi harus memiliki data yang konsisten dalam hal penulisan. Artinya, jika terdapat tanda petik, karakter noncetak, atau spasi, lakukanlah secara konsisten.
Tahukah Anda apa yang akan terjadi jika terdapat perbedaan penulisan pada salah satu nilai di kolom pertama tabel referensi?
Jika terjadi inkonsistensi, maka rumus Vlookup akan menghasilkan nilai #Value. Tanda #Value merupakan cara dari excel untuk menunjukkan bahwa ada yang salah dari penulisan rumusnya atau sel yang dijadikan referensi.
Nomor kolom
Nomor kolom merupakan urutan kolom pada sebuah tabel referensi yang berisi nilai atau data yang dicari melalui rumus Vlookup.
Perlu diketahui bahwa syarat mutlak yang harus dilakukan saat menuliskan nomor kolom adalah lebih dari nol (0). Jika Anda menuliskan nomor kolom dengan angka 0 atau –X, bisa dipastikan bahwa nilai yang akan dihasilkan adalah #Value.
Kesalahan juga dapat terjadi manakala nomor kolom yang ditulis pada rumus ternyata melebih jumlah kolom yang terdapat pada tabel referensi.
Hal ini akan menyebabkan rumus Vlookup akan menghasilkan nilai #Ref. Pesan error #Ref tersebut mengindikasikan bahwa sel referensi yang diajukan pada rumus ternyata tidak valid.
Perhatikan gambar tentang kesalahan penulisan nomor kolom berikut ini:
Keterangan:
Pada gambar di atas terpampang jelas beberapa contoh rumus yang salah. Agar lebih memahami contoh tersebut, simak dengan cermat ulasannya di bawah ini:
Rumus Pencarian “Nama”
=VLOOKUP(C10;A1:F8;2;FALSE)
Rumus Vlookup untuk “Nama” pada karyawan nomor 3 ( sel C10) memperoleh hasil yang sesuai yaitu Cici Sukaesih. Rumus tersebut terlihat jelas bahwa nomor kolom yang dituliskan telah sesuai yaitu “2” karena kolom “Nama” terletak pada urutan kedua.
Rumus Pencarian “Golongan”
=VLOOKUP(C10;A1:F8;0;FALSE)
Rumus Vlookup untuk “Golongan” pada karyawan nomor 3 ( sel C10) memperoleh hasil #Value. Hal ini disebabkan oleh sintaksis nomor kolom yang salah yaitu “0”. Penulisan nomor kolom seharusnya diisi angka “3” karena kolom “Golongan” terletak pada urutan ketiga.
Rumus Pencarian “Gaji”
=VLOOKUP(C10;A1:F8;-1;FALSE)
Rumus Vlookup untuk “Gaji” pada karyawan nomor 3 ( sel C10) memperoleh hasil yang #Value. Penulisan sintaksis pada nomor kolom juga terjadi kesalahan karena diisi dengan angka kurang dari nol (negatif) yaitu “-1”. Penulisn nomor kolom yang benar adalah angka “4” karena kolom “Gaji” terletak pada urutan keempat.
Rumus Pencarian “Bonus”
=VLOOKUP(C10;A1:F8;7;FALSE)
Rumus Vlookup untuk “Bonus” pada karyawan nomor 3 ( sel C10) memperoleh hasil #Ref. Hal ini disebabkan oleh kesalahan penulisan nomor kolom dimana angka yang dicantumkan melebihi jumlah kolom yang ada.
Anda bisa melihat bahwa nomor kolom yang dituliskan di rumus adalah “7”, sedangkan jumlah kolom pada tabel referensi hanya “6”. Adapun penulisan nomor kolom yang tepat adalah angka “5” karena kolom “Bonus” terletak pada urutan kelima.
Range lookup
Komponen penyusun rumus excel Vlookup yang terakhir adalah range lookup. Pada bagian ini harus diisi dengan nilai Boolean yang berupa “False” atau “True”.
Sebagai bahan pengetahuan, Boolean merupakan bahasa pemrograman yang menghasilkan dua tipe nilai yang tak lain adalah true atau false.
Masing-masing dari nilai range lookup yang diterapkan pada rumus Vlookup tentunya memiliki arti yang berbeda. Adapun penggunaan yang tepat untuk kedua nilai Boolean tersebut dapat dirinci sebagai berikut:
FALSE
Jika menggunakan nilai “False”, fungsi Vlookup hanya akan menemukan hasil yang sama persis saat melakukan pencarian di tabel referensi. Apabila tidak ditemukan nilai yang persis, muncullah pesan error #NA yang bermakna “No Answer” atau “Tidak ada jawaban”.
Perhatikan ilustrasi rumus Vlookup dengan “False” di bawah ini!
Keterangan:
Pada gambar di atas terlihat bahwa di bawah tabel referensi terdapat 2 tabel Vlookup. Masing-masing melakukan pencarian pada siswa nomor 3 (Sel C13) dan nomor 12 (Sel C18).
Hasil dari rumus Vlookup untuk sel C13 menghasilkan nilai yang sesuai dan sama persis karena siswa nomor 3 ada pada tabel referensi.
Berbeda halnya dengan rumus Vlookup untuk sel C18 yang menghasilkan nilai #NA. Hal ini dikarenakan sel C18 mewakili siswa dengan nomor 12, sedangkan jumlah siswa pada tabel referensi hanya berjumlah 10.
TRUE
Jika menggunakan nilai “True”, fungsi Vlookup tidak hanya melakukan pencarian pada nilai yang sama persis. Artinya, jika proses lookup atau pencarian tidak menemukan nilai yang sama, hasil yang akan ditampilkan adalah nilai yang mendekati. Akan tetapi, nilai tersebut tidak lebih dari nilai yang dicari.
Syarat mutlak yang harus dilakukan saat menggunakan “True” adalah nilai dari kolom pertama tabel referensi harus memiliki urutan yang naik. Dengan demikian, Vlookup dapat mengidentifikasi nilai yang paling mendekati bilamana tidak ada yang sama persis.
Catatan penting lainnya adalah range lookup “True” ternyata bisa dikosongkan. Hal ini tidak akan mempengaruhi hasil dari rumus excel Vlookup. Artinya, nilai yang dihasilkan antara menggunakan “True” dengan dikosongkan adalah sama.
Perhatikan ilustrasi rumus excel Vlookup dengan “True” di bawah ini!
Keterangan:
Gambar tersebut memperlihatkan ada 2 tabel yaitu yang menggunakan range lookup ‘True” dan yang dikosongkan. Keduanya mengarah pada siswa nomor 10. Akan tetapi, tabel referensi menunjukkan bahwa urutan nomor 10 tidak ada karena dari angka 9 langsung ke angka 11.
Oleh sebab itu, Boolean “True” mencari nilai yang paling mendekati 10 yaitu angka 9. Dengan demikian, data yang keluar adalah siswa ke-9 yaitu atas nama Nilam Saputri.
Penerapan Rumus Excel Vlookup dengan 2 buah Tabel Referensi
Fungsi Vlookup pada Ms. Excel ternyata tidak hanya berlaku pada 1 buah tabel referensi. Akan tetapi, pada beberapa kasus dapat terjadi 2 atau lebih tabel utama yang dijadikan sebagai referensi. Hal ini biasanya terjadi apabila terdapat perbedaan dari segi waktu antara tabel yang satu dengan yang lainnya.
Pada kasus yang melibatkan 2 buah pilihan atau lebih, rumus Vlookup harus digabungkan dengan formula lain untuk menyelesaikannya. Adapun rumus bantu tersebut adalah IF atau logical function.
Bagi Anda yang baru, disarankan untuk mengetahui / menguasai tentang rumus excel IF. Anda bisa menjelajah artikel kami tentang bentuk-bentuk pengembangan Excel IF pada tautan tersebut. Dengan begitu bisa mempraktekan kasus penggabungan Vlookup dan IF dengan mudah.
Bentuk sintaksis dari penggabungan Vlookup dan IF adalah:
=IF(Tes logika; VLOOKUP(Nilai yang dicari;Tabel referensi 1; Nomor kolom; Range lookup); VLOOKUP(Nilai yang dicari;Tabel referensi 2; Nomor kolom; Range lookup))
Perhatikan dengan baik rumus excel vlookup pada gambar di bawah ini!
Keterangan:
Pada gambar di atas terlihat 2 tabel referensi yang berisi laporan laba tahunan sebuah toko. Kedua tabel tersebut memiliki perbedaan dari segi periode waktunya yaitu tabel 1 tahun 2018 dan tabel 2 tahun 2019.
Terlihat jelas pada tabel Vlookup bahwa nilai yang dicari adalah laba kuartal III (Sel B16) pada tahun 2019 (Sel B15). Fungsi IF pada rumus ini berperan untuk memberikan pilihan apakah yang dikehendaki tahun 2018 atau 2019.
Bentuk sintaksis yang harus dituliskan menjadi:
=IF(B15=2018;VLOOKUP(B16;A2:E6;5;FALSE);VLOOKUP(B16;A9:E13;5;
FALSE))
Cara baca: Jika sel B15 adalah tahun 2018, maka rumus Vlookup yang berlaku adalah yang pertama (mengandung tabel referensi 1). Namun, jika sel B15 ternyata bukan tahun 2018, maka Vlookup yang berlaku adalah yang mengandung tabel referensi 2.
Penggunaan rumus tersebut akan menghasilkan nilai yang sesuai yaitu Rp4.373.000. Artinya, jumlah laba dari ketiga toko tersebut pada kuartal III tahun 2019 adalah sebesar Rp4.373.000.
Baca juga: Rumus Excel IF Lengkap, Bentuk-Bentuk Pengembangan dan Contohnya
Penerapan Rumus Excel Vlookup pada Kasus Konversi Rentang Nilai
Fungsi Vlookup juga bisa digunakan untuk mencari konversi dari suatu rentang nilai tertentu. Konversi digunakan untuk mengubah suatu nilai dari angka ke dalam grade huruf.
Sejatinya, fungsi konversi rentang nilai ini juga dapat dilakukan oleh rumus IF, namun lebih rumit dan panjang. Oleh sebab itu, rumus Vlookup ini bisa menjadi solusi yang terbaik karena jauh lebih praktis.
Hal yang sangat penting untuk Anda ketahui sebelum menggunakan rumus excel Vlookup untuk konversi rentang nilai adalah pemilihan range lookup yang tepat. Dalam kasus ini, Anda harus menggunakan “TRUE” agar tidak hanya mendapatkan nilai yang sama persis, namun juga nilai yang mendekati.
Contoh:
Pada gambar di atas ditampilkan deretan nilai ujian dari para peserta didik di kelas IX. Selain itu, terlihat pula kriteria penilaian untuk grade A, B, C, D, dan E pada rentang nilai tertentu. Grade tersebut menunjukkan pencapaian prestasi berdasarkan nilai yang diperolehnya.
Jika menggunakan rumus Vlookup pada kasus konversi rentang nilai, hal pertama yang harus dilakukan adalah menyederhanakan rentang tersebut. Caranya adalah dengan mengambil nilai terkecil dari tiap rentang nilai.
Jadi, dari tabel rentang nilai di atas, Anda bisa mengubahnya menjadi sebagai berikut:
| Rentang nilai | Grade |
| 0 56 66 73 86 | E D C B A |
Setelah menentukan acuan rentang nilai tersebut, masukkan tabel nilai siswa beserta tabel referensi rentang nilai ke dalam lembar kerja excel. Letakkan keduanya secara berdampingan sebagaimana terlihat pada gambar di bawah ini:
Keterangan:
Pada gambar di atas memperlihatkan lembar kerja excel yang berisi tabel nilai siswa dan tabel referensi rentang nilai. Selanjutnya, masukkan rumus Vlookup ke dalam kolom grade seperti biasa yaitu:
=VLOOKUP(Nilai yang dicari; Tabel referensi; Nomor kolom; Range Lookup)
Pada contoh tersebut dijelaskan bagaimana cara mencari grade untuk siswa nomor 7 dan nomor 10. Adapun rumus Vlookup dari kedua grade tersebut adalah:
Siswa nomor 7 (Nilai 65): =VLOOKUP(C8;F1:G6;2;TRUE)
Siswa nomor 10 (Nilai 95): =VLOOKUP(C11;Kriteria_penilaian;2;TRUE)
Kedua contoh tersebut memberikan sebuah opsi dimana penulisan tabel referensi bisa dilakukan dengan dua cara. Cara pertama adalah dengan memblok range, sedangkan cara kedua adalah dengan menamai range tersebut.
Cara menamai range cukup mudah yaitu dengan memblok tabel referensi dan ketikkan nama pada name box. Jika nama range lebih dari 2 kata, pisahkan keduanya dengan underscore ( _ ) atau biasa disebut dengan low line.
Jika Anda memahami dengan benar konsep penggunaan rumus excel Vlookup, berbagai macam bentuk pengembangannya dapat dikuasai dengan mudah. Pengetahuan ini sangatlah membantu dalam menyelesaikan pekerjaan yang berkaitan dengan database excel dalam berbagai sektor.
Editted: 19/06/2021 by IDNarmadi.