Dalam pengolahan data, pencarian nilai berdasarkan kategori atau periode sering kali dilakukan secara horizontal—misalnya dalam laporan keuangan bulanan, jadwal pelajaran, atau buku nilai. Di sinilah fungsi HLOOKUP berperan.
Artikel ini membahas fungsi HLOOKUP Excel, bentuk sintaks, serta beberapa contoh penggunaannya.
Terlampir file dari mana semua screenshot di artikel ini berasal.
File: fungsi-hlookup-excel.xlsx.
Pengertian dan Fungsi HLOOKUP di Excel
HLOOKUP (Horizontal Lookup) adalah fungsi yang memungkinkan kita mencari nilai di baris pertama suatu tabel, lalu mengambil data dari baris lain di kolom yang sama.
Sintaks HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup])
Parameter:
- lookup_value (wajib): Nilai yang dicari.
- table_array (wajib): Rentang data tempat pencarian dilakukan.
- row_index_num (wajib): Indeks baris dari mana hasil dikembalikan (baris pertama = 1).
- range_lookup (opsional): Nilai boolean untuk menentukan mode pencarian.
- TRUE (default atau bila tidak disertakan): Pencarian berdasarkan perkiraan.
- FALSE: Pencarian sama persis.
Kapan dan Mengapa Menggunakan HLOOKUP di Excel
- Data terorganisasi secara horizontal.
- Membuat pencatatan keuangan bulanan, buku nilai, atau penjadwalan, di mana periode atau kategori terentang sepanjang kolom-kolom worksheet.
Untuk data yang terorganisasi secara vertikal, gunakan VLOOKUP atau XLOOKUP.
Tips Penting agar Rumus HLOOKUP Berfungsi Akurat
- Pastikan baris teratas adalah baris di mana lookup_value.
- Pastikan lookup_value berada di baris pertama dari table_array.
- Jika rumus disalin ke cell lain dengan sumber data yang sama, gunakan table_array dengan referensi absolut.
- Gunakan range_lookup = FALSE untuk pencarian persis (exact match).
- Pastikan row_index_num tidak melebihi jumlah baris dalam table_array dan mengacu pada baris yang sesuai di tabel tersebut.
- Jangan gunakan merge cells di dalam table_array karena dapat mengganggu hasil.
Contoh Penggunaan Rumus HLOOKUP di Excel
Mencari Nilai Siswa Berdasarkan Nomor Identitas
Rumus berikut adalah rumus HLOOKUP dalam bentuk dasar.
=HLOOKUP(A5; A1:D3; 2; FALSE)Rumus berisi:
A5(lookup_value): Nilai yang dicari.A1:D3(range_lookup): Rentang di mana nilai A5 dicari dan nilai kembalian berada.2(row_index_num): Indeks dari range_lookup yang menunjuk pada baris nilai kembalian.FALSE(range_lookup): Rumus mencari nilai sama persis.
Menentukan Harga Barang Berdasarkan Kode Produk
Rumus di bawah mencari harga barang berdasarkan kode.
range_lookup menggunakan referensi absolut untuk menghindari perubahan referensi saat rumus disalinkan ke cell-cell lain.
=HLOOKUP(A5; $B$1:$D$2; 2; FALSE)
Approximate Match: Menghitung Diskon Belanja
Rumus menghitung besaran diskon yang didapat untuk pembelanjaan sebesar Rp 1.014.000. Hasil didapat dengan menggunakan pencarian perkiraan atau approximate search. Rumus menemukan nilai terbesar yang lebih kecil dari Rp 1.014.000, yaitu Rp 1.000.000 dan mengembalikan 20%.
=HLOOKUP(A5; B1:E2; 2; TRUE)
HLOOKUP dan IFNA: Mencegah Error #N/A Tampil
Rumus di bawah menggunakan fungsi IFNA untuk menangkap error #N/A jika nilai tidak ditemukan dan menggantikannya dengan teks Belum Ada.
=IFNA(HLOOKUP(A5; B1:E2; 2; FALSE); "Belum Ada")
HLOOKUP dan MATCH: Memilih Baris Otomatis
MATCH dapat digunakan untuk menjadikan rumus HLOOKUP lebih fleksibel.
Pada rumus di bawah, MATCH menggunakan nama produk untuk mencari posisi baris. Posisi ini digunakan oleh HLOOKUP sebagai row_index_num.
=HLOOKUP(B7; B1:G4; MATCH(A7; A2:A4; 0); FALSE)
HLOOKUP dan INDIRECT: Memilih Tabel Secara Fleksibel
Saat data sumber terbagi menjadi beberapa rentang atau tabel, gunakan fungsi INDIRECT untuk membantu fleksibilitas rumus HLOOKUP.
INDIRECT mengubah teks menjadi referensi. Pada rumus di bawah, A8 adalah nama range. Nama range ini kemudian digunakan oleh HLOOKUP sebagai table_array.
=HLOOKUP(B8; INDIRECT(A8); 2; FALSE)
HLOOKUP dan TEXTJOIN: Menggabung Teks untuk Membuat Rangkuman
Rumus berikut menggunakan kombinasi HLOOKUP dan TEXTJOIN untuk menciptakan kalimat utuh. Kalimat ini dapat digunakan sebagai rangkuman dokumen atau teks dashboard.
=TEXTJOIN(" "; TRUE; "Hasil ujian teori dan praktek"; A5; "adalah";
TEXTJOIN("/"; TRUE; HLOOKUP(A5; $B$1:$E$3; {2;3}; FALSE)))
Mengapa Rumus HLOOKUP Tidak Berfungsi?
| Kesalahan | Penyebab | Solusi |
|---|---|---|
| #N/A | Nilai yang dicari tidak ditemukan atau lebih kecil dari nilai terkecil di lookup_range. |
|
| #REF! | row_index_num melebihi jumlah baris table_array. |
|
| #VALUE! |
|
|
| Hasil tidak sesuai | range_lookup = TRUE dan baris pertama table_array tidak diurutkan. |
|
Penutup
Fungsi HLOOKUP di Excel adalah solusi praktis untuk pencarian data horizontal—terutama saat kategori atau periode disusun di baris pertama. Dengan memahami cara kerja range_lookup, penggunaan referensi absolut, dan kombinasi dengan fungsi lain seperti MATCH, INDIRECT, dan TEXTJOIN, kita dapat membuat rumus lebih fleksibel dan informatif.