Mencari Pasangan Nilai Secara Vertikal: Fungsi VLOOKUP

Fungsi Vlookup digunakan untuk mendapatkan nilai dari tabel referensi, secara vertikal, dengan menggunakan nilai pasangan pencarian. Nilai pencarian merupakan nilai di kolom pertama tabel referensi serta informasi yang dikembalikan ditentukan berdasarkan nomor kolom tabel.

Fungsi Vlookup

Dari gambar di atas, tabel referensi adalah H11:I15 (Lookup2), sedangkan data yang dicari adalah harga dari item pencarian ID B2211. Harganya didapat senilai $ 5,20.

Memahami Rumus Vlookup Excel

Rumus Vlookup excel secara umum didefenisikan sebagai :

=VLOOKUP (nilai_pencarian; rentang_tabel; no_kolom; [rentang_pencarian])

nilai_pencarian: nilai yang ingin dicari pasangannya pada tabel referensi (data).

rentang_tabel: area tabel referensi.

no_kolom: nomor kolom yang mengandung nilai pencarian.

rentang_pencarian [opsional]: Ada 2 pilihan pencarian pada tabel referensi, yaitu true (1) jika menggunakan nilai alternatif, jika tidak ditemukan nilai yang persis sama; false (0) jika menggunakan nilai yang eksak (nilai persis sama). Defaultnya 1.

Menggunakan Vlookup dengan Rentang Pencarian – True (1)

Pilihan rentang_pencarian bernilai True (1) digunakan untuk mendapatkan nilai alternatif (tidak eksak), di mana hasil yang diambil adalah yang sama atau lebih kecil dari nilai yang dicari. Misalkan jika kolom pada tabel referensi terdapat angka 70, 80, 90 … dan pasangannya. Lalu Anda mencari pasangan nilai 75 maka akan diambil nilai 70 sebagai nilai lebih kecil terdekat dari nilai 75.

Contoh penggunaan True:

Pada gambar di bawah digunakan rumus sebagai berikut: E15=VLOOKUP(D15;$D$6:$E$9;2;1)

Pada contoh di atas, nilai yang dicari adalah besarnya iuran untuk penghasilan 29000 (pada D15). Dengan menggunakan rumus vlookup (E15) dicari pasangan nilai 29000 pada tabel referensi. Terlihat tidak ada penghasilan yang persis nilainya 29000 pada tabel referensi. Karena yang digunakan rentang_pencarian bernilai true (1), atau tidak harus persis sama, maka hasil yang diambil adalah Iuran terkecil yang berada antara 1000 dan 30000 (karena 29000 berada diantara 1000 dan 30000), yaitu 30%.

Menggunakan Vlookup dengan Rentang Pencarian – False (0)

Kita dapat menggunakan jenis lookup ini jika diinginkan angka dengan nilai yang persis sama sesuai data pasangan.

Contoh penggunaan False:

Pada gambar digunakan rumus sebagai berikut: F14=VLOOKUP(D14;$D$6:$E$9;2;0);

Pada contoh di atas, karena yang digunakan rentang_pencarian bernilai false (0) atau harus bernilai persis sama dengan pasangan nilai, maka untuk nilai yang tidak persis sama, maka akan menghasilkan error not available (#N/A).

Baca juga: Menggunakan Rumus Index – Match, Rumus HLOOKUP sebagai alternatif Vlookup.

Hindari Rumus VLookup dengan Rentang_Pencarian Default

Secara default Vlookup mencari dengan menggunakan rentang pencarian perkiraaan (alternatif). Hal ini karena jika dikosongkan nilai rentang_pencarian secara default bernilai True (1). Ada baiknya jika pada rumus tidak dibiarkan kosong atau default karena dapat bermasalah ketika Anda tidak menyadari nilai rentang_pencarian dan ingin mendapatkan nilai pencarian yang eksak.

Pada contoh di atas dengan menggunakan rentang_pencarian default (sel G6) nilai menjadi tidak sesuai dengan data yang seharusnya. Sedangkan dengan menggunakan rentang_pencarian eksak (false) nilai sesuai dengan data pada tabel referensi, di mana Kunci merupakan barang nomor 12.

Menentukan Data yang Dicari Melalui Nomor Kolom Vlookup

Jika suatu tabel terdiri 3 kolom atau lebih, Anda dapat menentukan data kolom mana yang akan ditampilkan dengan mengubah no_kolom pada rumus Vlookup.

Pada contoh di atas, data yang diambil pada sel H6 adalah email yaitu pada kolom 4 dan data pada sel H10 adalah nama depan atau pada kolom 2 tabel referensi (H6:E14).

Hanya Mengambil Data Pertama yang Sesuai

Jika terdapat beberapa data yang sesuai dengan nilai pencarian, maka akan diambil data pada baris awal yang ditemukan.

Pada contoh di atas ada 3 nama barang yang namanya sama. Data yang diambil merupakan data yang pertamakali ditemukan yaitu sandal nomor 2.

Pencocokan dengan Wildcard (penggunaan tanda asterik)

Fungsi Vlookup dapat digunakan untuk mencari data dengan mencocokkan beberapa huruf awalnya saja dengan menambahkan tanda asterik pada nilai pencarian. Jika menggunakan tanda asterix maka rentang_pencarian harus bernilai false atau eksak.

Gambar di atas merupakan contoh penggunaan asterik untuk mencari data dengan huruf awal “gul” dan didapat nama barang guling dengan nomor 6.

Vlookup dengan 2 Kriteria Lookup

Anda dapat menggunakan rumus Match untuk membuat 2 kriteria lookup pada Vlookup. Untuk memasukkan fungsi Match caranya seperti contoh berikut ini:

H6=VLOOKUP(H4;B5:E13;MATCH(H5;B4:E4;0);0)

Pada contoh di atas, fungsi Match akan mengembalikan nilai 3 sesuai dengan posisi bulan februari pada area B4:E4. Nilai yang didapat adalah $ 5.194

Error #N/A pada Vlookup

Ketika menggunakan rumus Vlookup, Anda akan mendapatkan nilai error #N/A jika terjadi kesalahan, misalnya data tidak ditemukan. Nilai kesalahan berguna untuk menginformasikan bahwa data Anda tidak ada. Anda dapat membuat pesan kesalahan sesuai yang diinginkan jika terjadi kesalahan dengan fungsi IFNA.

Untuk membuat pesan error yang dikustomisasi caranya:

G7=IFNA(VLOOKUP(F7;C6:D20;2;FALSE);”Tidak ditemukan”)

Pada contoh di atas, tanpa fungsi IFNA, pada sel G6 maka pesan yang dikembalikan adalah #N/A. Sedangkan pada sel G7 setelah ditambahkan fungsi IFNA maka akan ditampilkan pesan yang sudah dikustomisasi yaitu “Tidak ditemukan”.

Catatan:

Jika rentang_pencarian bernilai default atau diisi TRUE, dan tidak ditemukan kecocokan data, VLOOKUP akan mencocokkan nilai terdekat yang lebih kecil dari nilai pencarian. Namun, VLOOKUP akan tetap mencocokkan nilai yang sama jika ada.

edwinls

Leave a Comment