Untuk mengambil nama barang dari tabel harga, ketik =VLOOKUP(A2;Harga!$A$2:$D$9;2;FALSE). Kode barang harus berada di kolom paling kiri tabel sumber, karena VLOOKUP hanya membaca ke kanan. Argumen terakhir FALSE mencari kecocokan tepat, tetapi tidak membedakan huruf besar dan kecil.
Sintaks VLOOKUP dan arti keempat argumennya
Bentuk umum rumusnya seperti ini:
=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
Argumen pertama, lookup_value (nilai yang dicari), adalah isi sel yang dicocokkan, misalnya kode barang di A2. Excel hanya membaca isi sel itu, bukan nama barangnya.
Lalu table_array (tabel): blok sel tempat data dicari. Blok ini harus memuat kolom kode di paling kiri dan kolom yang ingin diambil. Contoh di bawah memakai A2:D9, sehingga baris judul tidak ikut terbaca sebagai data.
Bagian col_index_num (nomor kolom) menunjuk urutan kolom di dalam tabel, bukan huruf kolom di lembar kerja. Angka 1 berarti kolom paling kiri, 2 kolom kedua, dan seterusnya.
Terakhir, range_lookup (pencarian rentang) menentukan cara mencocokkan. FALSE untuk pencocokan persis, TRUE untuk pencocokan perkiraan.
Versi: VLOOKUP tersedia di Excel 2016, 2019, 2021, 2024, 365, dan Excel web dengan cara penulisan yang sama. XLOOKUP tersedia di Excel 2021, 2024, 365, dan Excel web. Pemisah argumen mengikuti pengaturan regional atau pemisah daftar pada perangkat, bukan semata-mata bahasa antarmuka Excel. Regional Indonesia umumnya memakai titik koma (;), sedangkan regional Inggris atau AS memakai koma (,), sehingga rumusnya menjadi
=VLOOKUP(A2,Harga!$A$2:$D$9,2,FALSE). Semua contoh di bawah memakai titik koma. Jika Excel Anda menampilkan nama fungsi lain, cari padanannya di daftar fungsi.
Argumen keempat: FALSE atau TRUE
FALSE mencari kecocokan persis. Jika kode tidak ditemukan, hasilnya #N/A, dan kesalahan itu langsung terlihat.
Pada pencocokan tepat, karakter * dan ? di dalam nilai pencarian teks dibaca sebagai wildcard. Kode "ATK-00?" misalnya bisa cocok dengan ATK-001. Untuk mencari tanda * atau ? apa adanya, tulis ~* atau ~? di nilai pencarian.
Jika argumen keempat dihapus, Excel memakai TRUE. Pada daftar barang, kode yang sedikit berbeda bisa memunculkan barang lain tanpa peringatan. Gunakan FALSE untuk kode, nomor identitas, atau nama yang harus cocok tepat; gunakan TRUE untuk mencari berdasarkan tabel batas yang diurutkan menaik.
Jika kode yang sama muncul dua kali di tabel, FALSE mengambil kecocokan pertama dari atas dan mengabaikan sisanya. VLOOKUP tidak menjumlahkan semua kecocokan. Untuk menjumlahkan, gunakan SUMIF.
Kenapa tabel dikunci dengan $
Alamat Harga!A2:D9 tanpa tanda dolar ikut bergeser saat rumus disalin ke bawah. Di baris berikutnya alamatnya menjadi Harga!A3:D10, sehingga baris 2 keluar dari tabel. Tanda $ membuat alamat tetap menjadi Harga!$A$2:$D$9. Cara tercepat memasangnya adalah memblok tabel lalu menekan F4.
Kunci $ tidak membuat tabel ikut melebar. Barang baru yang ditambahkan di A10:D10 tidak terbaca oleh $A$2:$D$9, jadi perbarui rentang di semua rumus, misalnya menjadi $A$2:$D$10. Untuk daftar harga yang terus bertambah, ubah menjadi Excel Table: blok A1:D9, tekan Ctrl + T, beri nama TabelHarga di tab Table Design, lalu tulis =VLOOKUP(A2;TabelHarga;2;FALSE). Tabel ini otomatis melebar saat baris baru diisi tepat di bawahnya. Letak menu bisa sedikit berbeda antarversi.
Contoh data: daftar harga di toko alat tulis
Toko alat tulis kecil menyimpan daftar barangnya di sheet bernama Harga. Setiap ada pembeli, kasir menulis kode dan jumlah barang di sheet Nota. Rumus VLOOKUP mengisi nama dan harga secara otomatis, sedangkan rumus perkalian menghitung subtotal.
Sheet Harga berisi tabel berikut, dengan judul kolom di baris 1 dan data di A2:D9.
| Kode | Nama barang | Harga satuan | Stok |
|---|---|---|---|
| ATK-001 | Pulpen gel hitam | 4.500 | 120 |
| ATK-002 | Pensil 2B | 3.000 | 85 |
| ATK-003 | Penghapus putih | 2.500 | 60 |
| ATK-004 | Buku tulis 58 lembar | 5.000 | 200 |
| ATK-005 | Penggaris 30 cm | 3.500 | 45 |
| ATK-006 | Lem kertas kecil | 6.000 | 30 |
| ATK-007 | Map plastik | 2.000 | 150 |
| ATK-008 | Stapler kecil | 25.000 | 12 |
Sheet Nota berisi kode dan jumlah yang dibeli. Kolom nama, harga, dan subtotal masih kosong dan akan diisi rumus. Tabel ini memakai sel A1:E6.
| Kode | Jumlah | Nama barang | Harga satuan | Subtotal |
|---|---|---|---|---|
| ATK-001 | 5 | |||
| ATK-004 | 10 | |||
| ATK-008 | 2 | |||
| ATK-002 | 4 | |||
| ATK-006 | 3 |
Harga pada tabel ini hanya contoh. Harga di toko Anda bisa berbeda.
Menulis rumus VLOOKUP langkah demi langkah
- Klik sel C2 di sheet Nota.
- Ketik
=VLOOKUP(. - Klik sel A2 yang berisi kode ATK-001.
- Ketik
;. - Buka sheet Harga, blok sel A2:D9, lalu tekan F4 sampai alamatnya menjadi
$A$2:$D$9. - Ketik
;2;FALSE)lalu tekan Enter.
Sel C2 sekarang menampilkan Pulpen gel hitam. Rumus lengkapnya:
=VLOOKUP(A2;Harga!$A$2:$D$9;2;FALSE)
Di sebagian laptop, F4 harus ditekan bersama Fn. Jika tombol itu tidak mengubah alamat, tulis tanda $ secara manual.
Mengambil nama dan harga sekaligus
Kolom harga memakai rumus yang sama, hanya nomor kolomnya diganti menjadi 3. Tulis di sel D2:
=VLOOKUP(A2;Harga!$A$2:$D$9;3;FALSE)
Hasilnya 4.500. Kolom stok tidak dibutuhkan di nota, jadi nomor 4 tidak dipakai.
Setelah C2 dan D2 terisi, blok kedua sel itu, lalu tarik pegangan kecil di pojok kanan bawah sampai baris 6. Alamat A2 akan berubah mengikuti baris, sedangkan tabel tetap terkunci karena memakai $.
Menghitung total dari hasil VLOOKUP
Subtotal di kolom E dihitung dengan jumlah dikali harga. Di E2 tulis =B2*D2, lalu tarik ke E6. Harga dari VLOOKUP ikut dikalikan, sehingga subtotal tetap benar meskipun harga di sheet Harga berubah.
Letakkan total di E8 dengan =SUM(E2:E6). Setelah rumus nama, harga, dan subtotal disalin hingga baris 6, hasil nota adalah sebagai berikut.
| Kode | Jumlah | Nama barang | Harga satuan | Subtotal |
|---|---|---|---|---|
| ATK-001 | 5 | Pulpen gel hitam | 4.500 | 22.500 |
| ATK-004 | 10 | Buku tulis 58 lembar | 5.000 | 50.000 |
| ATK-008 | 2 | Stapler kecil | 25.000 | 50.000 |
| ATK-002 | 4 | Pensil 2B | 3.000 | 12.000 |
| ATK-006 | 3 | Lem kertas kecil | 6.000 | 18.000 |
Cek manual: 22.500 + 50.000 + 50.000 + 12.000 + 18.000 = 152.500
Pencocokan perkiraan untuk rentang nilai
Guru sering perlu mengubah nilai ujian menjadi huruf mutu. Nilai 92 menjadi A, nilai 68 menjadi C. Daftar batas seperti ini tidak punya kecocokan persis, karena 68 tidak tertulis di tabel. Di sinilah TRUE dipakai.
Sheet Nilai memakai tabel siswa di A1:C6 dan tabel batas di E1:F6.
| Nama siswa | Nilai ujian | Huruf mutu |
|---|---|---|
| Ratna Sari | 92 | |
| Dimas Pratama | 68 | |
| Sari Wulandari | 85 | |
| Yoga Setiawan | 39 | |
| Lestari Ningsih | 70 |
| Nilai minimum | Huruf mutu |
|---|---|
| 0 | E |
| 40 | D |
| 55 | C |
| 70 | B |
| 85 | A |
Tulis rumus di C2, lalu tarik ke C6:
=VLOOKUP(B2;$E$2:$F$6;2;TRUE)
TRUE mengambil hasil pada batas terbesar yang lebih kecil dari atau sama dengan nilai yang dicari; kolom batas wajib diurutkan menaik.
Nilai 92 jatuh pada batas 85, sehingga hurufnya A. Nilai 68 berada di antara 55 dan 70, sehingga hasilnya C. Nilai 39 menjadi E karena berada di antara 0 dan 40. Baris 0 berfungsi sebagai batas bawah, jadi nilai rendah tetap mendapat huruf.
| Nama siswa | Nilai ujian | Huruf mutu |
|---|---|---|
| Ratna Sari | 92 | A |
| Dimas Pratama | 68 | C |
| Sari Wulandari | 85 | A |
| Yoga Setiawan | 39 | E |
| Lestari Ningsih | 70 | B |
Nilai di bawah 0 menghasilkan #N/A karena tidak ada batas yang lebih kecil darinya. Nilai di atas 100 tetap mendapat A tanpa peringatan. Untuk mencegah itu, blok B2:B6, buka Data → Data Validation. Di tab Settings pilih Allow: Decimal, Data: between, Minimum 0, Maximum 100. Di tab Error Alert pilih Style: Stop, lalu klik OK. Validasi hanya membatasi ketikan baru. Nilai lama dan data hasil tempel bisa lolos, jadi periksa dengan Data → Data Validation → Circle Invalid Data.
Kesalahan bila tabel batas tidak berurutan
Pencocokan perkiraan mensyaratkan kolom batas diurutkan menaik. Jika batas disusun turun, misalnya 85, 70, 55, 40, 0, hasilnya dapat salah tanpa pesan error, atau justru menampilkan #N/A. Sebelum memakai TRUE, periksa urutan kolom nilai minimum.
Error yang sering muncul pada VLOOKUP
Satu pesan error dapat memiliki beberapa penyebab; periksa kemungkinan berikut secara berurutan.
| Pesan error | Penyebab | Cara memperbaiki |
|---|---|---|
| #N/A | Nilai yang dicari tidak ditemukan di kolom paling kiri. Sering karena spasi tersembunyi, angka tersimpan sebagai teks, atau argumen keempat salah. | Periksa panjang teks dengan LEN, bersihkan dengan TRIM, lalu samakan tipe data kedua sisi. |
| #REF! | Nomor kolom lebih besar dari jumlah kolom di tabel, misalnya 5 untuk tabel yang hanya 4 kolom. | Periksa lebar blok tabel, lalu samakan nomor kolom dengan posisi yang diinginkan. |
| #VALUE! | Nomor kolom kurang dari 1 atau berupa teks yang tidak dapat dikonversi menjadi angka. | Isi nomor kolom dengan angka bulat, mulai dari 1. |
| #NAME? | Nama fungsi salah tulis, misalnya VLKUP, atau rumus memakai nama rentang yang tidak dikenali Excel. | Periksa ejaan VLOOKUP dan nama sheet. Tulis nama sheet diikuti tanda seru, misalnya Harga!. |
Jika Excel menolak rumus saat dimasukkan, periksa tanda kurung dan pemisah argumen. Tanda kurung penutup atau titik koma yang hilang biasanya membuat rumus ditolak, bukan menghasilkan #NAME?.
Spasi di ujung teks paling sulit dilihat. Kode “ATK-001 ” tampak sama dengan “ATK-001”, tetapi panjangnya berbeda. Di sel bantu kosong, gunakan =LEN(A2) dan =TRIM(A2) untuk memeriksa kode di nota, lalu lakukan pemeriksaan serupa pada kode di tabel sumber. Hasil TRIM harus ditempel sebagai nilai ke sel asal. TRIM hanya menghapus spasi biasa. Spasi nonbreaking yang sering ikut dari hasil impor tidak ikut terhapus. Untuk itu, pakai =TRIM(SUBSTITUTE(A2;CHAR(160);" ")) di sel bantu, periksa hasilnya dengan LEN, lalu tempel sebagai nilai ke sel asal. Cara ini hanya menangani spasi nonbreaking berkode 160, bukan semua karakter tak terlihat.
Angka yang tersimpan sebagai teks biasanya ditandai segitiga hijau di pojok sel dan rata kiri. Pilih kolom tersebut, buka Data → Text to Columns, lalu klik Finish. Konversikan hanya data yang memang berupa bilangan. Pertahankan kode seperti 00101 dan kode lebih dari 15 digit sebagai teks, lalu samakan tipe data pada kedua sisi pencocokan.
Menyembunyikan error dengan IFERROR
Kadang kode memang tidak ada di daftar, dan nota tetap perlu terlihat rapi. IFERROR mengganti pesan error dengan teks.
Tambahkan baris 7 di sheet Nota berisi kode ATK-010 dengan jumlah 2. Kode itu tidak ada di daftar harga. Rumus di C7:
=IFERROR(VLOOKUP(A7;Harga!$A$2:$D$9;2;FALSE);"Kode tidak terdaftar")
Rumus di D7:
=IFERROR(VLOOKUP(A7;Harga!$A$2:$D$9;3;FALSE);0)
Rumus subtotal di E7 tetap =B7*D7. Setelah baris 7 ditambahkan, ubah total di E8 menjadi =SUM(E2:E7).
| Kode | Jumlah | Nama barang | Harga satuan | Subtotal |
|---|---|---|---|---|
| ATK-010 | 2 | Kode tidak terdaftar | 0 | 0 |
Cek manual: 22.500 + 50.000 + 50.000 + 12.000 + 18.000 + 0 = 152.500
Harga 0 membuat nota tampak selesai, padahal ada barang yang belum dihitung. Jangan memfinalkan nota selama masih ada baris berstatus “Kode tidak terdaftar”. Jika hanya #N/A yang ingin ditangani, pakai IFNA sebagai gantinya. Error lain, seperti #REF!, tetap terlihat.
IFERROR menutup semua jenis error, termasuk kesalahan rumus yang sebenarnya. Jika nomor kolom ditulis 5 pada tabel 4 kolom, rumus tetap menampilkan “Kode tidak terdaftar” dan kesalahannya tersembunyi. Pakai IFERROR setelah rumus dipastikan benar.
Mencari di sheet lain dan batasan VLOOKUP
Rumus di atas sudah mengambil data dari sheet lain, yaitu Harga!$A$2:$D$9. Tanda seru memisahkan nama sheet dari alamat sel. Jika nama sheet mengandung spasi, tulis dengan kutip tunggal, misalnya 'Daftar Harga'!$A$2:$D$9.
Menyimpan daftar harga di sheet terpisah memudahkan perawatan. Saat harga naik, cukup ubah angka di sheet Harga, dan nota yang masih aktif ikut berubah. Perubahan ini cocok untuk nota yang belum disahkan, bukan untuk arsip transaksi. Setelah nota disahkan, simpan salinan nilai atau PDF agar harga transaksi lama tidak berubah.
Jika daftar harga ada di file lain, rumus menyimpan alamat file tersebut, misalnya [Harga.xlsx]Harga!$A$2:$D$9. Kalau file sumber dipindah atau diganti nama, Excel bisa menampilkan pesan pembaruan tautan atau memakai nilai lama. Buka Data → Edit Links untuk memperbarui sumber. Letak menu ini bisa sedikit berbeda antarversi.
VLOOKUP hanya bisa mencari ke kanan. Jika kolom kunci berada di D dan nilai yang ingin diambil berada di C, VLOOKUP biasa tidak dapat mengambil nilai tersebut karena posisinya di sebelah kiri kolom kunci.
Ada juga masalah saat kolom disisipkan. Misalnya Anda menyisipkan kolom baru sebelum kolom C pada sheet Harga. Harga bergeser ke D, tetapi indeks 3 kini mengambil kolom C yang baru, bukan harga. Excel tidak memunculkan error, sehingga hasil yang salah sulit ditemukan.
Dua fungsi berikut bisa menjadi pengganti. XLOOKUP tersedia di Excel 2021 ke atas dan Excel web. Rumusnya mengambil harga dari kode di A2:
=XLOOKUP(A2;Harga!$A$2:$A$9;Harga!$C$2:$C$9)
INDEX-MATCH tersedia di semua versi dan bisa mencari ke kiri:
=INDEX(Harga!$C$2:$C$9;MATCH(A2;Harga!$A$2:$A$9;0))
Keduanya tidak bergantung pada nomor kolom, sehingga lebih aman saat kolom disisipkan.
Pertanyaan yang sering muncul
Kenapa VLOOKUP saya hasilnya #N/A padahal datanya ada?
Ikuti urutan pemeriksaan di bagian error di atas. Jika kode sudah bersih dan tipe datanya sama, cek awal blok tabel. Blok harus dimulai dari kolom kode, misalnya Harga!$A$2:$D$9. Blok yang dimulai dari B membuat VLOOKUP mencari kode di kolom nama barang.
Apakah VLOOKUP bisa mencari ke kolom sebelah kiri?
Tidak. Pakai XLOOKUP atau INDEX-MATCH seperti contoh di bagian batasan VLOOKUP.
Apa bedanya VLOOKUP dengan HLOOKUP?
VLOOKUP (“vertical”) mencari nilai di kolom paling kiri, lalu turun ke bawah dan mengambil nilai di kolom lain. HLOOKUP (“horizontal”) mencari nilai dengan bergerak mendatar di baris paling atas tabel, lalu mengambil hasil dari baris yang ditentukan pada kolom yang cocok. Untuk daftar yang tersusun dalam kolom, seperti daftar harga toko alat tulis di atas, VLOOKUP adalah pilihan yang tepat.
Kalau nota perlu menjumlahkan subtotal per kategori, lanjutkan ke SUMIF. Tambahkan dulu kolom kategori di sheet Harga, lalu ambil kategorinya ke nota dengan VLOOKUP memakai nomor kolom yang sesuai. Setelah itu, kolom kategori dan subtotal bisa dipakai sebagai rentang SUMIF.