Tutorial Rumus VLOOKUP Excel untuk Pemula
Fungsi Rumus VLOOKUP Excel.
Pada bahasan ini, kita akan mencoba menjelaskannya sesederhana mungkin dan sedetail mungkin.
Tujuannya, agar para pembaca yang belajar rumus ini bisa dengan mudah dan cepat mengerti tentang teorinya serta cara menggunakan rumus VLOOKUP ini.
Mari kita mulai.
Fungsi Rumus VLOOKUP Excel untuk Pemula
Apa itu VLOOKUP?
Fungsi VLOOKUP pada microsoft excel adalah suatu fungsi atau rumus untuk mencari nilai yang ditentukan dan mengembalikan nilai yang cocok dari kolom lain dalam tabel yang disusun secara vertikal.
Secara lebih teknis, fungsi VLOOKUP mencari nilai di kolom pertama dari rentang tertentu dan mengembalikan nilai di baris yang sama dari kolom lain.
VLOOKUP mendukung: pencocokan kira-kira dan pencocokan yang tepat, dan karakter pengganti (*?) untuk pencocokan parsial/sebagian.
Tujuan
Mencari nilai dalam tabel dengan mencocokkan kriteria di kolom pertama yang telah ditentukan.
Rumus
= VLOOKUP (nilai, tabel, nomor_indeks_kolom, [range_lookup])
Penjelasan
nilai - Nilai yang akan dicari di kolom pertama tabel. Ini bisa berupa nilai (angka, tanggal atau teks), dan referensi sel.
Tidak seperti angka dan referensi sel, nilai teks harus selalu diapit dengan "tanda kutip ganda".
table - Tabel tempat mengambil nilai.
Indeks_kolom - Nomor kolom dalam tabel tempat mengambil nilai.
range_lookup - [opsional] TRUE = perkiraan kecocokan (default). FALSE = sama persis. Opsional berarti: boleh dimasukkan, boleh tidak.
VLOOKUP tidak membedakan huruf besar / kecil, artinya karakter huruf besar dan huruf kecil diperlakukan sama.
Fungsi ini tersedia di semua versi Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003 dan yang lebih rendah.
VLOOKUP singkatan dari: Vertikal-Lookup. Kalau diartikan menurut bahasa biasa artinya Telusuri/Cari secara Vertikal.
Tujuan VLOOKUP adalah mendapatkan informasi dari tabel yang diatur seperti ini:
 |
| Sumber gambar: Exceljet.net |
Menggunakan Nomor Order di kolom B sebagai nilai pencarian, VLOOKUP bisa mendapatkan ID Customer, Amount, Name, dan State untuk Order apa pun.
Misal, untuk mendapatkan nilai Name untuk Order 1004, rumusnya adalah:
= VLOOKUP ( 1004 , B5: F9 , 4 , FALSE ) // hasil yang didapat "Sue Martin"
Penjelasan:
Agar mudah dimengerti maksud Rumus di atas, kita coba mengartikannya dengan bahasa biasa. Perhatikan huruf yang diberi warna di bawah, itu sama dengan isi rumus di atas.
Cari secara vertikal di kolom Order yang nilainya 1004 pada tabel/rentang B5:F9, ambil data yang ada di kolom 4, harus tepat.
Memang terlihat panjang kalau dibahasa biasakan, namun mungkin ini perlu dibahas agar pembaca yang belum faham cepat mengertinya.
Mungkin ada pertanyaan, kenapa FALSE diartikan harus tepat? Bukankah arti sebenarnya adalah SALAH
Silahkan, check lagi ke atas mengenai penjelasan range_lookup untuk mendapatkan jawabannya.
Berikut contoh pengambilan referensi dari sel.
 |
| Gambar: ablebit.com |
Tadi, angka 1004 kita ketikkan di rumus secara langsung, nah untuk contoh ini kita ambil lewat referensi sel, yaitu di E1.
Perbedaan VLOOKUP dan HLOOKUP
Agar lebih mengerti tentang VLOOKUP ini, perhatikan gambar berikut:
 |
| Sumber gambar: ablebits.com |
Bisa dilihat, jika VLOOKUP Judulnya (Planet-Diameter) mendatar sedangkan HLOOKUP menurun.
Jadi, jika ada data dengan Judul mendatar, disitu kita bisa aplikasikan rumus VLOOKUP. Jika judulnya menurun, kita gunakan rumus HLOOKUP.
Karena Judul untuk VLOOKUP mendatar, maka otomatis isi datanya menurun atau mengikuti arah kolom.
Nah itulah yang disebut dengan Vertikal.
Sedangkan HLOOKUP kebalikannya.
Mudah bukan? Pasti sekarang pembaca sudah mengerti dengan yang dimaksud dengan Vertikal-Lookup atau VLOOKUP
Mari kita lanjutkan bahasannya agar lebih faham lagi.
VLOOKUP Didasarkan pada Nomor Kolom
Saat kita menggunakan VLOOKUP, bayangkan setiap kolom dalam tabel diberi nomor, dimulai dari kiri.
Untuk mendapatkan nilai dari kolom tertentu, berikan nomor yang sesuai sebagai "indeks kolom".
Misalnya, indeks kolom untuk mengambil nilai First di bawah ini adalah 2:
 |
| Sumber gambar: exceljet.net |
Last dan Email dapat diambil di kolom 3 dan 4. (Perhatikan angka 2 3 4 pada gambar)
Rumus:
= VLOOKUP ( H3 , B4: E13 , 2 , FALSE ) // nilai dari First
= VLOOKUP ( H3 , B4: E13 , 3 , FALSE ) // nilai dari Last
= VLOOKUP ( H3 , B4: E13 , 4 , FALSE ) // alamat email
Perhatikan juga tanda panah kuning di atas pada gambar, itu menunjukkan rumus pada kolom H4.
Aturan VLOOKUP
VLOOKUP hanya dapat melihat ke kanan. Data yang ingin Anda ambil (nilai hasil) bisa muncul di kolom mana pun di sebelah kanan nilai pencarian:
 |
| Sumber gambar: ecxeljet.net |
Jika kita perlu mencari nilai di sebelah kiri, gunakan INDEX dan MATCH, atau XLOOKUP .
Nilai Absolute
Pencocokan Tepat dan Perkiraan (True vs False)
VLOOKUP memiliki dua mode pencocokan, tepat dan perkiraan. Nama argumen yang mengontrol pencocokan adalah "range_lookup".
Ini adalah nama yang membingungkan, karena sepertinya ada hubungannya dengan rentang sel seperti A1: A10.
Sebenarnya, kata "range" dalam kasus ini mengacu pada "kualitas nilai rentang" - jika range_lookup TRUE VLOOKUP akan cocok dengan rentang nilai, tapi tidak persis.
Penting untuk dipahami bahwa range_lookup defaultnya TRUE, yang berarti VLOOKUP akan menggunakan perkiraan pencocokan secara default, yang bisa berbahaya (tidak valid).
Setel range_lookup ke FALSE untuk memaksa pencocokan tepat:
= VLOOKUP (nilai, tabel, indeks_kol) // perkiraan kecocokan (default)
= VLOOKUP (nilai, tabel, indeks_kol , TRUE ) // perkiraan kecocokan
= VLOOKUP (nilai, tabel, indeks_kol, FALSE ) // kecocokan tepat
Catatan: kita juga dapat memasukkan nol (0) dan bukan FALSE untuk pencocokan tepat.
Pencocokan Tepat (FALSE)
Dalam kebanyakan kasus, kita mungkin ingin menggunakan VLOOKUP dalam mode pencocokan tepat.
Ini bisa diaplikasikan bila kita memiliki kunci unik yang digunakan sebagai nilai pencarian, misalnya, judul film (Movie) dalam data ini:
 |
| Sumber gambar: exceljet.net |
Rumus dalam H6 untuk mencari nilai Year, berdasarkan data pada Movie yang sama persis, adalah:
= VLOOKUP ( H4, B5: E9, 2, FALSE ) // FALSE = sama persis
Perkiraan Kecocokan (TRUE)
Jika kita menginginkan kecocokan terbaik, belum tentu sama persis, kita dapat menggunakan mode perkiraan.
Misalnya, di bawah ini kita ingin mencari tingkat komisi (Comm %) di tabel G5: H10. Nilai pencarian berasal dari kolom C.
Dalam contoh ini, kita perlu menggunakan VLOOKUP dalam mode pencocokan perkiraan, karena dalam banyak kasus, kecocokan tepat tidak akan pernah ditemukan.
Rumus VLOOKUP di D5 dikonfigurasi untuk melakukan perkiraan kecocokan dengan mengatur argumen terakhir ke TRUE:
 |
| Gambar: exceljet |
= VLOOKUP ( C5, $G$5: $H$10, 2, TRUE) // TRUE = perkiraan kecocokan
VLOOKUP akan memindai nilai di kolom G untuk nilai pencarian. Jika yang sama persis ditemukan, VLOOKUP akan menggunakannya.
Jika tidak, VLOOKUP akan "mundur" dan mencocokkan dengan baris sebelumnya.
Catatan: data harus diurutkan dalam urutan menaik (ascending) menurut nilai pencarian saat kita menggunakan mode perkiraan pencocokan (TRUE) dengan VLOOKUP.
Pencocokan Pertama
Dalam kasus nilai duplikat, VLOOKUP akan mengambil kecocokan pertama saat mode kecocokan tepat (FALSE).
Pada layar di bawah ini, VLOOKUP dikonfigurasi untuk menemukan harga untuk warna "Green".
Ada tiga entri dengan warna Green, dan VLOOKUP mengembalikan harga entri pertama, $17 (lihat panah).
Rumus di sel F5 adalah:
= VLOOKUP ( E5 , B5: C11 , 2 , FALSE ) // mengembalikan 17
Pencocokkan Wildcard (Karakter Pengganti)
Fungsi VLOOKUP mendukung karakter pengganti, yang memungkinkan untuk melakukan pencocokan parsial/sebagian pada nilai pencarian.
Misalnya, kita dapat menggunakan VLOOKUP untuk mengambil nilai dari tabel setelah mengetikkan hanya sebagian dari nilai pencarian.
Untuk menggunakan karakter pengganti dengan VLOOKUP, kita harus menentukan mode pencocokan tepat dengan memberikan FALSE atau 0 untuk argumen terakhir, range_lookup.
Rumus di H7 mengambil nama depan (First), "Michael", setelah mengetik "Aya" di sel H4:
= VLOOKUP ( $ H $ 4 & "*" , $ B $ 5: $ E $ 104, 2, FALSE )
Pencarian Dua Arah
Di dalam fungsi VLOOKUP, argumen indeks kolom biasanya disetel sebagai angka statis.
Namun, kita juga dapat membuat indeks kolom dinamis dengan menggunakan fungsi MATCH untuk menemukan kolom kanan.
Teknik ini memungkinkan kita membuat pencarian dua arah yang dinamis, yang cocok pada baris dan kolom.
Pada layar di bawah ini, VLOOKUP dikonfigurasi untuk melakukan pencarian berdasarkan Nama (Name) dan Bulan (Month).
Rumus di H6 adalah:
= VLOOKUP ( H4 , B5: E13 , MATCH ( H5, B4: E4 , 0 ), 0 )
Catatan: Secara umum, INDEX dan MATCH adalah cara yang lebih fleksibel untuk melakukan pencarian dua arah .
Vlookup Berbagai Kriteria
Fungsi VLOOKUP tidak menangani beberapa kriteria secara asli. Namun, kita bisa menggunakan kolom pembantu untuk menggabungkan beberapa bidang, dan menggunakan bidang ini dengan beberapa kriteria di dalam VLOOKUP.
Dalam contoh di bawah ini, Kolom B adalah kolom pembantu yang menggabungkan nama depan dan belakang bersama dengan rumus ini:
= C5 & D5 // kolom pembantu
VLOOKUP dikonfigurasi untuk melakukan hal yang sama untuk membuat nilai pencarian.
Rumus di H6 adalah:
= VLOOKUP ( H4 & H5 , B5: E13 , 4 , 0 )
 |
| Gambar: exceljet |
Penjelasan:
H4 isinya: Jon, H5 isinya: Victor, maka ketika disatukan (H4&H5) menjadi JonVictor.
VLOOKUP dan Kesalahan #N/A
Jika kita menggunakan VLOOKUP, kita pasti akan mengalami kesalahan #N/A.
Kesalahan #N/A berarti "tidak ditemukan". Misalnya, di layar di bawah ini, nilai pencarian "Toy Story 2" tidak ada di tabel pencarian, dan ketiga rumus VLOOKUP mengembalikan #N/A:
 |
| Gambar: exceljet |
Salah satu cara untuk "menjebak" kesalahan NA adalah dengan menggunakan fungsi IFNA seperti ini:
 |
| Gambar: exceljet |
Rumus di H6 adalah:
= IFNA ( VLOOKUP ( H4 , B5: E9 , 2 , FALSE ), "Not found" )
Pesan "not found" tersebut dapat disesuaikan sesuai keinginan, misalnya "tidak ditemukan".
Untuk tidak mengembalikan apa-apa (yaitu untuk menampilkan hasil kosong) ketika VLOOKUP mengembalikan #N/A kita dapat menggunakan string kosong seperti ini:
= IFNA ( VLOOKUP ( H4 , B5: E9 , 2 , FALSE ), "" ) // tidak ada pesan
Kesalahan #N/A berguna karena memberi tahu kita ada sesuatu yang salah.
Cara Vlookup Beda Sheet di Excel
Dalam praktiknya, fungsi VLOOKUP Excel jarang digunakan dengan data di lembar kerja yang sama. Paling sering kita harus menarik data yang cocok dari lembar kerja yang berbeda.
Untuk Vlookup dari lembar Excel yang berbeda, letakkan nama worksheet diikuti dengan tanda seru dalam argumen table_array sebelum referensi rentang.
Misalnya, untuk mencari dalam rentang A2: B10 di Sheet2, gunakan rumus ini:
=VLOOKUP("Product1", Sheet2!A2:B10, 2)
Tentu saja, kita tidak perlu mengetikkan nama sheet secara manual. Cukup, ketikkan rumus dan ketika sampai pada argumen table_array, beralihlah ke worksheet pencarian dan pilih rentang menggunakan mouse.
Misalnya, kita mencari nilai A2 di kisaran A2: A9 pada worksheet Price dan mengembalikan nilai yang cocok dari kolom C:
=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)
 |
| Gambar: ablebits.com |
Catatan:
Jika nama spreadsheet berisi spasi atau karakter non-alfabet, maka harus diapit dengan tanda kutip tunggal, misalnya 'Daftar harga'! $ A $ 2: $ C $ 9.
Jika kita menggunakan rumus VLOOKUP untuk beberapa sel, ingatlah untuk mengunci table_array dengan tanda $, seperti $ A $ 2: $ C $ 9.
Cara Vlookup Beda File di Excel
Untuk Vlookup dari buku kerja Excel yang berbeda, letakkan nama buku kerja tersebut dalam tanda kurung siku sebelum nama lembar kerja.
Misalnya, berikut rumus untuk mencari nilai A2 pada lembar bernama Price di file Price_List.xlsx :
=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)
Jika nama buku kerja atau nama lembar kerja berisi spasi atau karakter non-alfabet, kita harus mengapitnya dalam tanda kutip tunggal seperti ini:
=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$9, 3, FALSE)
Cara termudah untuk membuat rumus VLOOKUP yang merujuk ke buku kerja berbeda adalah ini:
- Buka kedua file.
- Mulai ketikkan rumus kita, alihkan ke buku kerja di file lain, dan pilih larik tabel menggunakan mouse.
- Masukkan argumen yang tersisa dan tekan tombol Enter untuk menyelesaikan rumusnya.
Hasilnya akan terlihat seperti gambar di bawah ini:
 |
| Gambar: ablebits.com |
Setelah kita menutup file, rumus VLOOKUP akan terus bekerja, dan akan menampilkan alamat lengkap acuan:
 |
| Gambar: ablebits.com |
Cara Vlookup dari Rentang Bernama di Sheet Lain
Jika kita berencana menggunakan rentang pencarian yang sama dengan banyak rumus, kita dapat membuat rentang bernama untuk itu dan mengetikkan namanya secara langsung dalam argumen table_array .
Untuk membuat rentang bernama, cukup pilih sel dan ketik nama yang kita inginkan di kotak Nama di sebelah kiri bilah Formula.
Untuk contoh, kita beri nama Prices_2020 ke sel data (A2: C9) di lembar pencarian dan mendapatkan rumus ringkas ini:
=VLOOKUP(A2, Prices_2020, 3, FALSE)
 |
| Gambar: ablebits.com |
Kebanyakan nama di Excel berlaku untuk seluruh worksheet, jadi kita tidak perlu menentukan nama lembar kerja saat menggunakan rentang bernama
Jika rentang bernama ada di sheet lain, letakkan nama buku kerja sebelum nama rentang, misalnya:
=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)
Rumus seperti itu jauh lebih bisa dimengerti, bukan? Selain itu, menggunakan rentang bernama bisa menjadi alternatif yang baik untuk referensi absolut.
Karena rentang bernama tidak berubah, kita yakin bahwa larik tabel akan tetap terkunci di mana pun rumus dipindahkan atau disalin.
Penutup
Jika pembaca merasa ada yang belum faham dengan penjelasan di sini, silahkan dibaca ulang dari awal.
Kemudian teliti satu per satu contoh rumus yang ada, dan dilihat penjelasannya lagi. Jangan hanya dibaca sekilas.
Selain itu, silahkan dipraktekan juga dengan membuat contoh tabel buatan sendiri.
Ketika itu sudah dilakukan, maka kita akan cepat mengerti dengan maksud dan rumus yang tersedia.
Baca juga: Cara Menggunakan Fungsi SUMIF Microsoft Excel