Halo teman-teman semua! Di artikel ini saya akan membagikan tutorial tentang cara menggunakan rumus VLOOKUP dan HLOOKUP di Excel. Dua rumus ini sangat penting dan sering digunakan untuk mencari data secara otomatis, terutama kalau teman-teman bekerja dengan banyak tabel, daftar nilai, daftar produk, atau jenis data lainnya.
Saya akan menjelaskan mulai dari konsep dasarnya, perbedaan antara VLOOKUP dan HLOOKUP, hingga cara mempraktekkannya langsung di Excel lengkap dengan contoh kasus yang mudah diikuti. Gaya penjelasannya akan saya buat santai seperti percakapan, supaya teman-teman bisa memahami materi ini dengan lebih cepat.
Artikel ini juga saya kemas dengan sangat detail agar cocok dipublikasikan di blog dan mudah ditemukan mesin pencari.
Langsung saja, mari kita mulai!
File latihan Excel yang saya gunakan, bisa teman-teman download di link ini: https://drive.google.com/file/d/1SpgVsJmM55BFL3nKeOnr1FeSn7_BLBWN/view
Apa Itu VLOOKUP dan HLOOKUP?
Sebelum masuk ke teknis rumusnya, teman-teman perlu paham dulu konsep dasarnya:
- VLOOKUP
VLOOKUP adalah singkatan dari Vertical Lookup, artinya pencarian dilakukan secara vertikal, dari atas ke bawah.
Tabel referensi yang digunakan berbentuk kolom menurun.
Ciri-ciri tabel untuk VLOOKUP:
- Judul kolom ada di bagian atas.
- Data tersusun memanjang ke bawah.
- Kolom paling kiri adalah acuan pencarian.
- HLOOKUP
HLOOKUP adalah singkatan dari Horizontal Lookup, artinya pencarian dilakukan secara horizontal, dari kiri ke kanan.
Tabel referensi berbentuk baris mendatar.
Ciri-ciri tabel untuk HLOOKUP:
- Judul tabel berada di samping kiri.
- Data tersusun ke kanan.
- Baris paling atas adalah acuan pencarian.
Jadi, teman-teman bisa simpulkan:
- Kalau tabel bantuan/datanya vertikal → gunakan VLOOKUP
- Kalau tabel bantuan/datanya horizontal → gunakan HLOOKUP
Dengan memahami konsep dasar ini, teman-teman akan lebih mudah menentukan kapan harus memakai rumus yang mana.
Contoh Kasus: Mengisi Data Secara Otomatis Menggunakan VLOOKUP dan HLOOKUP
Supaya lebih jelas, kita langsung gunakan contoh kasus seperti yang saya buat di video.
Misalnya kita punya dua tabel:
Tabel 1 (Sheet 1) → Tabel yang akan kita isi otomatis
Berisi kolom:
- ID Produk
- Nama Produk
- Kategori
- Harga
- Satuan
Di tabel ini, teman-teman hanya mengetikkan ID Produk, sementara kolom lainnya ingin diisi otomatis menggunakan VLOOKUP dan HLOOKUP.
Tabel Referensi (Sheet 2)
Tabel asli yang berisi daftar produk lengkap, misalnya:
| ID Produk | Nama Produk | Kategori | Harga | Satuan |
| 201 | Roti Gandum | Makanan | 15.000 | bungkus |
| 202 | Jus Apel | Minuman | 12.000 | liter |
| 203 | Pembersih Lantai | Peralatan Kebersihan | 10.000 | botol |
| 204 | Teh Hitam | Minuman Kesehatan | 20.000 | box |
| 205 | Keju Mozzarella | Produk Susu | 25.000 | blok |
Tabel seperti inilah yang nantinya dijadikan dasar pencarian.
Bagian 1: Cara Menggunakan Rumus VLOOKUP di Excel
Format Dasar Rumus VLOOKUP
Format umum rumusnya adalah:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Penjelasan:
- lookup_value → nilai acuan pencarian (misalnya ID Produk)
- table_array → tabel referensi tempat mencari data
- col_index_num → nomor kolom yang ingin diambil hasilnya
- range_lookup →
- FALSE / 0 = pencarian harus cocok 100%
- TRUE / 1 = pencarian mendekati (kurang cocok untuk data seperti ID Produk)
Dalam penggunaan tabel referensi, kita harus mengunci (lock) range tabel menggunakan simbol $ supaya tidak berubah ketika rumus disalin ke bawah.
Simbol $ ini bisa otomatis muncul dengan menekan tombol F4 atau FN + F4 di laptop tertentu.
Langkah-Langkah VLOOKUP (Contoh Lengkap)
Kita akan mulai mengisi kolom “Nama Produk” berdasarkan ID Produk.
- Pilih sel tujuan
Misalnya di kolom Nama Produk (Sheet 1).
- Ketik rumus dasar
=VLOOKUP(
- Isi lookup_value
Klik sel ID Produk yang ada di baris yang sama, misalnya sel B4:
=VLOOKUP(B4,
- Tentukan table_array
Pindah ke Sheet 2 → blok tabel referensi tanpa judul kolom, misalnya A4:E8:
=VLOOKUP(B4,Sheet2!A4:E8,
- Kunci table_array
Tekan F4 sampai muncul simbol dolar:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,
- Isi col_index_num
Kolom ke berapa yang ingin diambil nilainya?
Misalnya Nama Produk berada di kolom ke-2:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,2,
- Pilih range_lookup
Karena kita ingin hasil yang 100% sesuai (exact match), isi dengan:
FALSE
Rumus lengkapnya:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,2,FALSE)
Tekan ENTER → Nama produk otomatis muncul.
- Salin rumus ke bawah
Teman-teman bisa:
- Drag sudut kanan bawah sel
- Atau cukup double click sudut kanan bawah sel (lebih cepat)
Semua baris akan terisi otomatis.
Mengisi Kolom Lain Menggunakan VLOOKUP
Prinsipnya sama, hanya ganti col_index_num:
- Kategori berada di kolom ke-3 → isi angka 3
- Harga kolom ke-4 → isi angka 4
- Satuan kolom ke-5 → isi angka 5
Contoh rumus kategori:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,3,FALSE)
Harga:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,4,FALSE)
Satuan:
=VLOOKUP(B4,Sheet2!$A$4:$E$8,5,FALSE)
Apa yang Terjadi Jika Tidak Menggunakan Tanda Dolar ($)?
Tanpa mengunci tabel referensi, Excel akan menggeser alamat tabel saat rumus disalin, sehingga rentang datanya berubah.
Contoh konsekuensinya:
- Muncul error N/A
- Data tidak ditemukan
- Hasil pencarian tidak akurat
Makanya sangat penting untuk mengunci tabel menggunakan $A$4:$E$8.
Selisih FALSE dan TRUE pada VLOOKUP
- FALSE (Exact Match)
Digunakan untuk pencarian yang harus sama persis seperti ID Produk, NIK, nomor invoice, dll. - TRUE (Approximate Match)
Untuk pencarian nilai rentang seperti nilai ujian, ranking, tarif, dll.
Biasanya tidak cocok untuk data produk.
Jadi untuk tutorial ini, selalu gunakan:
FALSE
Bagian 2: Cara Menggunakan Rumus HLOOKUP di Excel
HLOOKUP digunakan ketika tabel referensi berbentuk horizontal. Format rumusnya:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Penjelasan:
- lookup_value → nilai acuan pencarian
- table_array → tabel referensi mendatar
- row_index_num → baris ke berapa yang ingin Anda ambil datanya
- range_lookup → TRUE/FALSE
Contoh Penggunaan HLOOKUP
Misalnya tabel referensi berbentuk seperti ini:
| ID Produk | 201 | 202 | 203 | 204 | 205 |
| Nama Produk | Roti Gandum | Jus Apel | Pembersih Lantai | Teh Hitam | Keju Mozzarella |
| Kategori | Makanan | Minuman | Peralatan Kebersihan | Minuman Kesehatan | Produk Susu |
| Harga | 15.000 | 12.000 | 10.000 | 20.000 | 25.000 |
| Satuan | bungkus | liter | botol | box | blok |
Rumus HLOOKUP untuk Nama Produk
=HLOOKUP(B4,Sheet2!$A$4:$E$8,2,FALSE)
Perbedaan penting:
- Di HLOOKUP, kita mengambil row_index_num.
- Baris nama produk biasanya berada di baris ke-2 pada tabel horizontal.
Untuk kolom lainnya:
- Baris ke-3 → kategori
- Baris ke-4 → harga
- Baris ke-5 → satuan
Bagian 3: Cara Update Data Jika Ada Penambahan Produk Baru
Ini bagian yang sering membingungkan pemula.
Misalnya kita menambah data baru di tabel referensi:
Dari awal:
A4:E8
Setelah ditambah:
A4:E11
Masalah yang muncul:
- VLOOKUP tetap membaca A4:E8
- Data di baris 9, 10, 11 tidak terbaca
- Hasil tabel tetap N/A meskipun datanya sudah ada
Solusi Cepat: Gunakan Fitur Replace
- Salin teks E8
- Tekan Ctrl+H (Replace)
- Tempel “E8” di kotak “Find what”
- Isi “E11” pada ”Replace with”
- Klik Replace All
- Selesai! Semua rumus otomatis diperbarui.
Ini jauh lebih cepat daripada mengedit rumus satu per satu.
Bagian 4: Tips Penting Agar VLOOKUP dan HLOOKUP Tidak Error
Berikut beberapa tips yang sering saya gunakan:
- Selalu kunci table_array dengan F4
Ini sangat penting untuk mencegah rentang berubah.
- Gunakan FALSE untuk pencarian exact match
Hindari TRUE kecuali benar-benar dibutuhkan.
- Bersihkan spasi sebelum pencarian
Kadang error muncul hanya karena ada spasi tidak terlihat.
- Pastikan lookup_value berada di kolom/baris pertama tabel referensi
Kalau tidak, rumus tidak akan berjalan.
- Gunakan IFERROR untuk hasil lebih rapi
Contoh:
=IFERROR(VLOOKUP(B4,Sheet2!$A$4:$E$11,2,FALSE),”Data Tidak Ada”)
Kesimpulan
Rumus VLOOKUP dan HLOOKUP adalah dua rumus penting yang harus dikuasai semua pengguna Excel, baik pemula maupun profesional.
Dengan rumus ini, teman-teman dapat:
- Mempercepat pengisian data
- Mencari data otomatis berdasarkan ID atau kode
- Menghindari pekerjaan manual yang memakan waktu
- Mengurangi kesalahan input data
Di artikel ini, saya sudah menjelaskan mulai dari konsep dasar, contoh penggunaan, tips, hingga solusi ketika data perlu ditambah. Teman-teman bisa langsung mempraktekkan langkah-langkahnya di Excel.
Silakan dicoba, dan kalau berhasil, teman-teman pasti akan lebih cepat bekerja menggunakan Excel.
Terima kasih sudah membaca artikel panjang ini. Semoga bermanfaat, dan sampai jumpa di tutorial saya berikutnya!