excel.my.id
Pencarian & ReferensiExcelGoogle Sheets

VLOOKUP

Diperbarui

VLOOKUP mencari sebuah nilai di kolom paling kiri tabel, lalu mengambil isi kolom lain pada baris yang sama. Pakai fungsi ini untuk menarik harga, stok, atau nama dari tabel master berdasarkan kode atau nama. Untuk kecocokan persis, isi argumen terakhir dengan FALSE (atau 0).

Formula dasar

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
ArgumenArtiWajib?
lookup_valueNilai yang dicari. Harus ada di kolom pertama table_arrayYa
table_arrayRentang tabel. Kolom paling kiri berisi nilai yang dicariYa
col_index_numNomor kolom yang isinya diambil, dihitung dari kolom pertama tabel (1, 2, 3, …)Ya
range_lookupFALSE atau 0 = persis. TRUE, 1, atau kosong = perkiraan, kolom pertama harus urut naikTidak

Contoh: harga dan stok dari tabel produk

Tabel master ada di A1:C4:

Kolom 1ABC
1ProdukHargaStok
2Apel500010
3Mangga80005
4Jeruk300020

Sel B6 berisi nama yang dicari. Hasil rumus berubah sesuai isi B6:

Isi B6RumusHasil
Apel=VLOOKUP(B6, A2:C4, 2, 0)5000
Apel=VLOOKUP(B6, A2:C4, 3, 0)10
Kiwi=VLOOKUP(B6, A2:C4, 2, 0)#N/A
Kiwi=IFERROR(VLOOKUP(B6, A2:C4, 2, 0), "Tidak ada")Tidak ada

Angka 2 dan 3 adalah nomor kolom di dalam tabel A2:C4: kolom B urutan kedua (harga), kolom C urutan ketiga (stok).

Pencocokan perkiraan untuk tarif bertingkat

Dengan argumen terakhir TRUE, VLOOKUP mengambil baris terdekat yang nilainya tidak melebihi nilai yang dicari. Misalnya diskon berdasarkan total belanja, tabel di E2:F4 (urut naik):

Belanja (Rp)Diskon
00%
10000005%
500000010%

=VLOOKUP(3000000, E2:F4, 2, TRUE) menghasilkan 5%, karena 3.000.000 berada di antara 1.000.000 dan 5.000.000.

Simulasi: Fungsi VLOOKUP

Cari harga produk dari tabel master lewat nama. Pelajari cara kerja VLOOKUP langkah demi langkah.

B7 =VLOOKUP(B6, A2:C4, 2, 0)
Simulasi VLOOKUP. Sel biru bisa diubah, sel tebal berisi rumus.
ABC
1ProdukHargaStok
2Apel500010
3Mangga80005
4Jeruk300020
6CariApelNama yang dicari
7Harga5.000=VLOOKUP(key, table, col, 0)
8Stok10Ambil kolom ke-3

Kolom biru = input yang bisa diedit. Ganti nama produk untuk mencari harga lain.

Langkah demi langkah
  1. 1. Apa itu VLOOKUP?

    VLOOKUP mencari nilai di kolom pertama tabel, lalu mengembalikan nilai dari kolom yang ditentukan di baris yang sama. =VLOOKUP(key, table, col_index, 0).

    Lihat juga: HLOOKUP

  2. 2. Siapkan Tabel

    Tabel master (A2:C4) berisi Produk, Harga, Stok. VLOOKUP butuh kolom pencari di posisi paling kiri.

  3. 3. Argumen ke-4 = 0

    Angka terakhir 0 berarti kecocokan exact (tepat sama). Pakai 1 atau TRUE hanya untuk rentang numerik terurut.

  4. 4. Ambil Kolom

    B7 = VLOOKUP(B6, A2:C4, 2, 0) mengambil kolom ke-2 (Harga). B8 kolom ke-3 (Stok). Ubah B6 jadi Mangga/Jeruk untuk melihat hasil berubah.

  5. 5. Kalau Tidak Ketemu

    Jika nama tidak ada di tabel, VLOOKUP mengembalikan #N/A. Bungkus dengan IFERROR agar tampil pesan ramah. Alternatif modern: XLOOKUP.

    Lihat juga: IFERROR, XLOOKUP

  6. Pelajaran Kunci

    VLOOKUP hanya bisa mencari ke kanan. Butuh cari ke kiri? Pakai INDEX + MATCH atau XLOOKUP.

    Lihat juga: INDEX, MATCH, XLOOKUP

Kesalahan umum

GejalaPenyebabPerbaikan
#N/ANilai tidak ditemukan: salah ketik, spasi di ujung, atau angka vs teks ("1001" tidak sama dengan 1001)TRIM pada nilai yang dicari, samakan tipe data, atau bungkus dengan IFERROR / IFNA
Hasil salah tapi tidak errorArgumen terakhir kosong (default TRUE) pada data yang tidak urutSelalu tulis FALSE untuk pencarian persis
#REF!col_index_num lebih besar dari jumlah kolom di table_arrayPeriksa jumlah kolom pada rentang
#VALUE!col_index_num kurang dari 1 atau bukan angkaIsi dengan angka bulat mulai 1
Rumus ditarik ke bawah lalu bergesertable_array relatifKunci dengan $A$2:$C$4 atau pakai tabel bernama
Rumus ditolak atau “terlalu banyak argumen”Pengaturan regional memakai koma desimal, sehingga pemisah argumen biasanya titik komaTulis =VLOOKUP(B6;A2:C4;2;0)

Excel vs Google Sheets, dan penggantinya

  • Sintaks dan hasil sama di keduanya. Nama argumen terakhir saja yang berbeda (range_lookup di Excel, is_sorted di Sheets).
  • VLOOKUP tidak bisa mengambil ke kiri dan akan salah bila Anda menyisipkan kolom di dalam tabel, karena col_index_num berupa angka tetap.
  • XLOOKUP mengatasi kedua masalah itu dan memakai pencocokan persis secara default. XLOOKUP tersedia di Excel 2021, Excel 365, dan Google Sheets, tetapi tidak di Excel 2019 atau yang lebih lama. Di versi lama, pakai INDEX + MATCH.
  • Pakai VLOOKUP bila file akan dibuka orang dengan Excel lama; pakai XLOOKUP untuk file baru.

Sumber: VLOOKUP di Microsoft Support dan VLOOKUP di Google Docs Editors Help.

Tips

  • Tulis FALSE setiap kali, bahkan bila tabel Anda sudah urut. Kebiasaan ini mencegah hasil salah diam-diam.
  • Gunakan COLUMN() atau MATCH untuk col_index_num bila kolom bisa bergeser: =VLOOKUP(B6, A2:C4, MATCH("Stok", A1:C1, 0), 0).
  • Jangan gunakan seluruh kolom (A:C) pada file besar yang banyak rumus VLOOKUP; batasi ke rentang data.
  • Satu nilai muncul dua kali di kolom pertama? VLOOKUP hanya mengambil yang pertama.

Lihat juga

HLOOKUP untuk tabel yang melebar ke samping, XLOOKUP, INDEX, MATCH, IFERROR. Kasus yang sering muncul: VLOOKUP #N/A padahal data ada.

Pertanyaan yang sering muncul

Kenapa VLOOKUP menghasilkan

Penyebab paling sering ada tiga. Nilai yang dicari tidak persis sama (spasi tersembunyi di ujung teks), angka di satu sisi tersimpan sebagai teks, atau argumen terakhir kosong sehingga VLOOKUP memakai pencocokan perkiraan pada data yang belum urut. Bersihkan dengan TRIM, samakan tipe datanya, dan isi argumen terakhir dengan FALSE.

Apa bedanya FALSE dan TRUE di argumen terakhir VLOOKUP?

FALSE (atau 0) mencari kecocokan persis dan memberi #N/A bila tidak ada. TRUE (atau dikosongkan) mencari nilai terdekat yang tidak melebihi nilai yang dicari, dan hanya benar bila kolom pertama tabel sudah urut naik. TRUE cocok untuk tabel tarif atau konversi nilai ke predikat.

Bisakah VLOOKUP mengambil data dari kolom di sebelah kiri?

Tidak. VLOOKUP hanya mencari di kolom pertama tabel dan mengambil ke kanan. Untuk mengambil ke kiri pakai XLOOKUP atau kombinasi INDEX dan MATCH.

Apakah VLOOKUP sama di Excel dan Google Sheets?

Sintaks dan hasilnya sama. Di Google Sheets argumen terakhir bernama is_sorted, di Excel bernama range_lookup, tetapi urutan dan perilakunya sama.

Cari di excel.my.id

Coba cari