Pertemuan 7 – Fungsi VLOOKUP, HLOOKUP, Lookup String, dan IF Lookup

Capaian Pembelajaran

Setelah pertemuan ini mahasiswa mampu mengambil data dari tabel referensi menggunakan VLOOKUP dan HLOOKUP, menangani kesalahan #N/A dengan benar, melakukan lookup berdasarkan pola teks, serta menggabungkan fungsi lookup dengan IF untuk hasil yang dinamis.

VLOOKUP (Pencarian Vertikal)

=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup]) mencari lookup_value pada kolom pertama table_array lalu mengembalikan nilai dari kolom yang sama barisnya sesuai col_index_num.

• Contoh =VLOOKUP(E2;$A$2:$D$11;3;FALSE) mencari kode pada E2 di kolom A, lalu mengambil nilai dari kolom ketiga (Harga).
• range_lookup FALSE: pencarian eksak, disarankan untuk kode atau nama produk. TRUE memberi pendekatan (approximate) yang mensyaratkan data kolom pertama diurutkan naik.
• Kolom pertama table_array harus memuat nilai kunci pencarian.
• col_index_num dihitung mulai kolom pertama table_array, bukan kolom lembar kerja mutlak.
• Gunakan referensi absolut ($A$2:$D$11) agar tabel tidak bergeser saat formula disalin ke bawah.

HLOOKUP (Pencarian Horizontal)

=HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup]) bekerja seperti VLOOKUP, namun baris pertama table_array menjadi kunci pencarian dan nilai diambil dari baris yang ditentukan.

• Contoh =HLOOKUP(E2;$A$1:$D$11;4;FALSE) mencari E2 pada baris 1 lalu mengambil nilai dari baris ke-4.
• Ship sesuai tabel ringkasan horizontal seperti matriks harga per kuartal atau per semester.

Menangani Kesalahan Pencarian

Ketika nilai tidak ditemukan, fungsi mengembalikan #N/A. Bungkus dengan IFERROR agar tampil pesan yang ramah pengguna:

=IFERROR(VLOOKUP(E2;$A$2:$D$11;3;FALSE);"Data Tidak Ditemukan")

IFERROR menangkap semua jenis kesalahan dan menggantinya dengan pesan atau nilai yang Anda tentukan. Alternatif lain adalah memeriksa terlebih dahulu dengan =IF(COUNTIF($A$2:$A$11;E2)>0;VLOOKUP(…);”Tidak Ada”).

Lookup pada String dan IF Lookup

Pencarian tidak selalu berdasarkan nilai eksak. Untuk pola teks kombinasikan fungsi pencarian teks dengan IF.

• =IF(ISNUMBER(SEARCH(“Join”;B2));”Produk A”;”Produk Lain”) memeriksa keberadaan substring dalam teks.
• Kombinasi =IFERROR(VLOOKUP(“voucher”&”*”;tabel;2;FALSE);””) memanfaatkan wildcard pada lookup.
• Teknik IF Lookup: =IF(B2=”A”;80;IF(B2=”B”;70;60)) menyusun tabel keputusan sederhana berbasis IF berantai.
• SEARCH tidak peka huruf besar-kecil; gunakan FIND bila perlu sensitifitas kasus.

Catatan XLOOKUP

Versi Excel terbaru menyediakan XLOOKUP yang lebih fleksibel: =XLOOKUP(nilai; array_pencarian; array_result; [jika_tidak]; [mode_cocok]; …). Berbeda dengan VLOOKUP, XLOOKUP tidak membatasi kunci pada kolom pertama, tidak menghitung indeks kolom, dan dapat mencari ke dua arah. Namun karena tidak dibahas rinci pada materi utama, fokus latihan tetap pada VLOOKUP dan HLOOKUP sebagai keterampilan dasar yang masih dominan digunakan di lingkungan kerja.

Metode dan Aktivitas (120 Menit)

1. Penjelasan konsep lookup dan tabel referensi (30 menit).
2. Demonstrasi VLOOKUP dan HLOOKUP dengan studi kasus (40 menit).
3. Praktik IFERROR, lookup string, dan IF lookup (40 menit).
4. Diskusi kelebihan XLOOKUP (10 menit).

Latihan Praktik

1. Buat tabel referensi Produk berisi Kode, Nama, Harga, dan Stok.
2. Gunakan VLOOKUP untuk mengisi Nama, Harga, dan Stok pada tabel transaksi berdasarkan kode.
3. Bungkus dengan IFERROR agar kode yang tidak dikenal menampilkan pesan jelas.
4. Buat tabel matriks harga horizontal lalu gunakan HLOOKUP.
5. Gunakan IF bersama SEARCH untuk mengelompokkan produk berdasarkan kata kunci.

Ringkasan

VLOOKUP dan HLOOKUP adalah fondasi pengambilan data antar tabel. Kombinasi dengan IFERROR, pola teks, dan IF memperkaya penggunaannya pada data nyata. Berlatih dengan referensi absolut dan memahami pencarian eksak versus pendekatan akan menghindari kesalahan umum pada analisis lanjutan.

Contoh Kasus Langkah Demi Langkah

Sebuah toko memiliki tabel transaksi pada kolom A berisi Kode Produk, dan tabel referensi Produk pada kolom F sampai I berisi Kode, Nama, Harga, dan Stok. Isi Nama dengan =VLOOKUP(A2;$F$2:$I$101;2;FALSE), Harga dengan col_index_num 3, dan Stok dengan col_index_num 4, lalu salin ke bawah. Karena pedoman pencarian eksak FALSE, kode yang tidak dikenal menghasilkan #N/A; bungkus setiap lookup dengan IFERROR agar menampilkan “Tidak Ada” atau nilai 0.

Untuk matriks harga per kuartal yang disusun horizontal, gunakan HLOOKUP. Misal baris pertama berisi nama produk dan baris kedua berisi harga kuartal, maka =HLOOKUP(G2;$A$1:$D$20;2;FALSE) mengambil harga berdasarkan nama. Untuk kelompok produk berdasarkan kata kunci, gunakan =IF(ISNUMBER(SEARCH(“voucher”;B2));”Voucher”;”Produk Lain”). Gabungkan pola teks dan VLOOKUP dengan wildcard, misal =IFERROR(VLOOKUP(“voucher*”;$A$2:$B$101;2;FALSE);”Tidak Ditemukan”) untuk mencocokkan rangkaian kode yang diawali voucher.

Soal Diskusi

• Mengapa col_index_num dihitung dari kolom pertama table_array, bukan dari kolom lembar kerja?
• Kapan sebuah lookup sebaiknya memakai range_lookup FALSE dibanding TRUE?
• Bagaimana IFERROR meningkatkan kualitas hasil pencarian yang tidak ketemu kodenya?
• Apa kelebihan XLOOKUP dibanding VLOOKUP dan kapan Anda akan beralih menggunakannya?

Tugas Mandiri

Buat dua tabel: TabelTransaksi dengan kolom Kode Produk minimal 30 baris, dan TabelReferensi dengan kolom Kode, Nama, Harga, dan Stok untuk 15 produk. Gunakan VLOOKUP untuk mengisi seluruh kolom acuan pada tabel transaksi, lengkapi dengan IFERROR, lalu buat satu kasus pengelompokkan hasil lookup berdasarkan kata kunci memakai IF dan SEARCH. Ringkas hasilnya dalam laporan singkat berisi langkah, rumus yang dipakai, dan kendala yang ditemukan.