VLOOKUP di Excel, langkah demi langkah
VLOOKUP mencari sebuah nilai di kolom pertama tabel dan mengembalikan nilai dari baris yang sama, beberapa kolom di sebelah kanan. Fungsi ini dipakai untuk membawa harga ke lembar pesanan, nama ke daftar ID karyawan, atau wilayah ke daftar toko.
=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
lookup_valueadalah nilai yang Anda cari, biasanya sebuah sel sepertiE2.table_arrayadalah tabel yang dicari, dan kolom pertamanya harus kolom yang berisi nilai yang Anda cari.col_index_numadalah kolom yang akan dikembalikan, dihitung dari tepi kiri tabel: 1 adalah kolom pertama tabel, bukan kolom A di sheet.range_lookupadalahFALSEuntuk pencocokan eksak. Kalau dikosongkan, Excel memakai pencocokan perkiraan.
Di Excel berbahasa Indonesia, argumen dipisahkan dengan titik koma. Kalau Excel Anda memakai koma sebagai pemisah daftar, hal yang umum bila pemisah desimalnya titik, tulis rumus yang sama dengan koma.
Contoh yang diselesaikan
Daftar harga berada di A1:C5, dan lembar pesanan membutuhkan harga untuk kode di E2:
| A | B | C | |
|---|---|---|---|
| 1 | Kode | Produk | Harga |
| 2 | K-104 | Ketel | 34,00 |
| 3 | T-220 | Pemanggang roti | 29,50 |
| 4 | M-310 | Mixer | 89,00 |
| 5 | B-415 | Blender | 54,00 |
=VLOOKUP(E2;$A$2:$C$5;3;FALSE)
Dengan T-220 di E2, VLOOKUP menemukan kode itu di baris 3 sheet dan mengembalikan kolom ketiga tabel: 29,50. Tanda dolar mengunci tabel, jadi saat Anda menyalin rumus ke bawah untuk pesanan berikutnya, rentangnya tetap di tempat dan hanya E2 yang berpindah ke E3.
Pencocokan eksak dan perkiraan
Pakai FALSE setiap kali Anda mencocokkan kode, ID, atau nama. Dengan TRUE, atau kalau argumen terakhir dikosongkan, VLOOKUP menganggap kolom pertama terurut dari yang terkecil ke yang terbesar dan mengembalikan baris dengan nilai terbesar yang kurang dari atau sama dengan nilai yang Anda cari. Itu tepat untuk pencarian berjenjang, seperti tarif komisi menurut tingkat penjualan atau biaya kirim menurut berat. Pada data yang tidak terurut, hasilnya baris yang salah tanpa peringatan, jadi tambahkan FALSE setiap kali Anda mencocokkan nilai eksak.
Error #N/A
#N/A berarti nilai tidak ditemukan di kolom pertama. Penyebab yang biasa adalah salah ketik, spasi di akhir salah satu dari kedua nilai, atau angka yang tersimpan sebagai teks di satu sisi dan sebagai angka di sisi lain. TRIM menghapus spasi berlebih, dan VALUE mengubah teks yang tampak seperti angka menjadi angka. Kalau nilai yang hilang memang wajar, bungkus rumusnya agar menampilkan sesuatu yang terbaca:
=IFNA(VLOOKUP(E2;$A$2:$C$5;3;FALSE);"Tidak ada di daftar harga")
Dari sheet lain
Tulis nama sheet dan tanda seru sebelum rentangnya: =VLOOKUP(E2;Harga!$A$2:$C$200;3;FALSE). Kalau nama sheet mengandung spasi, apit dengan tanda kutip tunggal: 'Daftar harga'!$A$2:$C$200.
Batas VLOOKUP, dan XLOOKUP
VLOOKUP hanya melihat ke kanan, jadi tidak bisa mengembalikan kolom di sebelah kiri kolom yang dicari. Nomor kolomnya tidak ikut bergeser saat seseorang menyisipkan kolom ke dalam tabel, sehingga rumusnya diam-diam mulai mengembalikan kolom yang salah. Fungsi ini juga hanya mengembalikan kecocokan pertama. Di Excel untuk Microsoft 365 dan Excel 2021, XLOOKUP mengatasi dua masalah pertama: =XLOOKUP(E2;A2:A5;C2:C5;"Tidak ada di daftar harga") langsung menunjuk ke kolom yang dikembalikan dan punya nilai tersendiri untuk kasus tidak ditemukan. Di versi yang lebih lama, INDEX bersama MATCH mengerjakan hal yang sama.
Latihan di pelajaran ini dikerjakan di spreadsheet yang berfungsi seperti Excel. Ketik rumusnya, tekan Enter, dan hasilnya diperiksa di sel, jadi rumus benar apa pun akan lolos.