Fungsi HLOOKUP pada Excel, bersama dengan LOOKUP dan VLOOKUP, adalah salah satu fungsi lookup di Excel.
Tulisan kami kali ini membahas bagaimana menggunakan rumus HLOOKUP Excel berikut contoh pemakaiannya.
File: fungsi-hlookup-excel-2.xlsx.
Apa Itu Fungsi HLOOKUP pada Excel?
Fungsi HLOOKUP adalah fungsi yang mencari suatu nilai di baris teratas suatu range data dan mengembalikan nilai lain yang berada pada kolom yang sama dengan nilai yang dicari ditemukan.
HLOOKUP digunakan pada tabel yang berorientasi horisontal. Pada rumus ini, nilai-nilai yang dibandingkan berada di baris teratas, sementara nilai-nilai yang ingin diambil berada di bawahnya.
Untuk membantu, H di HLOOKUP berarti Horizontal. Secara keseluruhan, HLOOKUP berarti Horizontal Lookup.
Sintaks
HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup])
- lookup_value (wajib) adalah nilai yang dicari. lookup_value dapat berupa nilai apa pun dengan tipe data apa pun dan case insensitive.
- table_array (wajib) adalah range di mana lookup_value dicari dan nilai yang ingin dikembalikan berada. table_array dapat berupa referensi cell atau nama cell.
- Tabel berorientasi horisontal.
- Bila range_lookup bernilai TRUE, maka data pada kolom pencarian harus terurut secara ascending. Bila FALSE, tidak harus.
- row_index_num (wajib) adalah posisi baris di dalam range. row_index_num menentukan posisi nilai kembalian di dalam table_array.
- range_lookup (opsional) adalah nilai logika untuk menentukan apakah HLOOKUP mencari nilai yang sama persis (FALSE) atau mirip (TRUE). Bila tidak dituliskan, range_lookup diasumsikan bernilai TRUE.
Excel mensyaratkan agar data pada baris pencarian diurutkan secara ascending bila parameter ini bernilai TRUE. Bila FALSE, maka pengurutan tidak dibutuhkan.
Saat bernilai TRUE dan tidak ada nilai cocok, HLOOKUP akan mencari nilai terbesar yang lebih kecil dari lookup_value.
Bila tidak ditemukan sama sekali, HLOOKUP akan mengembalikan pesan error #N/A.
Perbedaan HLOOKUP dan VLOOKUP
Perbedaan HLOOKUP dan VLOOKUP terletak pada orientasi atau arah pencarian lookup_value.
HLOOKUP akan mencari data secara horisontal. Fungsi ini mencari nilai di sepanjang baris teratas range.
Nilai yang dikembalikan HLOOKUP berada pada kolom yang sama di mana nilai ditemukan.
Sementara itu, VLOOKUP mencari data secara vertikal. Fungsi ini mencari nilai di sepanjang kolom terkiri range.
Nilai yang dikembalikan VLOOKUP berada pada baris yang sama dengan di mana nilai ditemukan.
Cara Menggunakan Rumus HLOOKUP Excel
Contoh HLOOKUP Sederhana
Rumus HLOOKUP Excel paling sederhana hanya menggunakan tiga dari empat parameter.
=HLOOKUP("Dunia Safari"; B1:E7; 4)
- Dunia Safari (lookup_value) adalah nilai yang dicari. HLOOKUP akan mencari Dunia Safari di baris teratas B1:E7. Berhubung teks, nilai diapit oleh kutip ganda. Bila angka, tidak perlu kutip ganda.
- B1:E7 (table_array) adalah array di mana nilai-nilai perbandingan lookup_value dan nilai-nilai kembalian berada.
- 4 (row_index_num) adalah posisi baris di dalam array di mana nilai-nilai kembalian berada.
Pastikan setiap parameter terpisah oleh pemisah daftar. Kami menggunakan pemisah daftar titik koma.
Dalam bentuk kalimat rumus di atas dibaca sebagai: Cari Dunia Safari di baris teratas range B1:E7. Bila ketemu, kembalikan nilai pada baris ke empat dari kolom di mana Dunia Safari ditemukan.
Perhatikan B1:D7 terurut secara ascending. Dengan tidak menuliskan nilai bagi range_lookup, rumus akan menggunakan nilai default TRUE. Nilai ini mengharuskan data terurut ascending.
Contoh HLOOKUP Excel: Nilai Mirip atau Sama
Bila nilai yang dicari tidak ketemu, HLOOKUP akan mencari nilai terbesar yang lebih kecil dari lookup_value.
Parameter keempat HLOOKUP, yaitu range_lookup, digunakan untuk menentukan apakah kita ingin mencari nilai yang sama atau mirip.
=HLOOKUP(A10; $B$1:$E$7; 4;TRUE)
=HLOOKUP(A11; $B$1:$E$7; 4; FALSE)
Kedua rumus di atas masing-masing mencari Dunia Safaro (cell A10 dan A11) di range B1:E7.
Pada rumus pertama (range_lookup = TRUE), HLOOKUP tidak menemukan Dunia Safaro. Dunia Safari adalah nilai terbesar yang lebih kecil dari Dunia Safaro, maka rumus mengambil nilai ini sebagai acuan bagi nilai kembalian. Karenanya, HLOOKUP mengembalikan 53000.
Bila teman-teman periksa tabel ASCII, o (ASCII 111) lebih besar dari huruf i (ASCII 105).
Pada rumus kedua, range_lookup bernilai FALSE. HLOOKUP mencari nilai yang sama persis, yaitu Dunia Safaro. Berhubung tidak ada, maka pesan error #N/A tampil.
Kami menggunakan referensi absolut bagi range B1:E7 agar referensi tersebut tidak berubah saat disalin ke bawah.
Cara Menggunakan Rumus HLOOKUP dan LEFT
Teman-teman pasti pernah lihatin NIK di KTP dan sadar bahwa nomor tersusun dari kode-kode tertentu, bukan angka acak.
Kadang kita juga menggunakan kode-kode serupa di dalam data kita sendiri dan memerlukan sebagian dari kode tersebut untuk mengambil data lain.
Di Excel, fungsi LEFT digunakan untuk mengambil sebagian teks dari sisi kiri.
Rumus berikut menghitung besaran komisi berdasarkan bagian tertentu dari kode komisi dan besaran penjualan.
=HLOOKUP(LEFT(A5; 2); $B$1:$D$2; 2; FALSE)*C5
Rumus menggunakan LEFT untuk mengambil dua huruf terkiri dari Kode Komisi (A5:A11). Dua huruf tersebut digunakan untuk mendapatkan persentase komisi dari array B1:D2.
Hasil dari setiap baris kami kalikan dengan Penjualan (C5:C11) untuk mendapatkan komisi.
Rumus HLOOKUP dan RIGHT
Rumus berikut merupakan lanjutan dari rumus sebelumnya.
Rumus mengambil teks dari sisi kanan menggunakan fungsi RIGHT dan memberikan hasilnya ke HLOOKUP sebagai lookup_value untuk mendapatkan besaran bonus yang sesuai.
=HLOOKUP(RIGHT(A8; 1),$B$4:$D$5; 2)*D8
Hasil kembalian dari HLOOKUP kami kalikan dengan Komisi (D8:D14) untuk mendapatkan besaran bonus komisi.
Rumus HLOOKUP Beda Sheet
Sumber data tidak selalu berada pada sheet yang sama.
Tidak jarang kita menempatkan sumber data pada sheet yang beda atau bahkan workbook lain.
Bagian ini adalah modifikasi rumus yang sudah ada sebelumnya, tapi kami buat pada sheet yang berbeda.
Ikuti langkah-langkah berikut untuk menggunakan rumus HLOOKUP Excel beda sheet:
- Ketikkan
=HLOOKUP(. - Pilih cell A2 sebagai lookup_value, tambahkan pemisah daftar.
- Buka worksheet Gambar 02.
- Pilih range B1:E7, lalu tambahkan pemisah daftar.
- Ketikkan
4sebagai posisi baris nilai kembalian. - Ketikkan
)untuk mengakhiri rumus. - Tekan Enter.
=HLOOKUP(A2; 'Gambar 02'!B1:E7; 4)
Pada langkah-langkah di atas, kami mengambil data pada array dari sheet yang berbeda.
Pada kodisi lapangan, tidak hanya array_lookup, tapi lookup_value pun bisa berada pada sheet yang berbeda.
Untuk rumus HLOOKUP berbeda file, caranya sama saja. Pastikan file sumber terbuka.
Untuk menuliskan rumus secara langsung tanpa harus pindah worksheet atau workbook (file), pastikan teman-teman tahu bagaimana cara menuliskan referensi worksheet atau workbook dengan tepat di dalam rumus.
Rumus IF dan HLOOKUP
Fungsi IF adalah fungsi pengendali keputusan.
Bila dikombinasikan dengan rumus HLOOKUP Excel, salah satu kemungkinannya adalah kita melakukan evaluasi terhadap nilai kembalian HLOOKUP.
=IF(HLOOKUP(A5; $B$1:$G$2; 2; FALSE)>=7500000000; HLOOKUP(A5; $B$1:$G$2; 2; FALSE); 0)
Rumus di atas mencari dan mengembalikan nilai-nilai dari range B1:G2 berdasarkan lookup_value di range B5:B10.
Nilai-nilai hasil HLOOKUP diperiksa apakah lebih besar atau sama dengan 7,500,000,000.
Bila ya, IF akan mengembalikan nilai tersebut. Bila tidak, 0 akan ditampilkan.
Contoh Rumus HLOOKUP Excel dengan Wildcard
HLOOKUP memperbolehkan kita menggunakan wildcard untuk mencari nilai-nilai dengan kriteria teks tertentu.
Excel mengenal tiga wildcard, yaitu:
- Tanda asterisk (*) untuk mewakili satu atau lebih teks yang tidak diketahui.
- Tanda tanya (?) untuk mewakili satu karakter yang tidak diketahui.
- Tanya tilde (~) untuk menganulir satu tanda asterisk atau tanda tanya yang diletakkan setelahnya.
| Rumus | Penjelasan |
|---|---|
=HLOOKUP("*an*"; B1:F2; 2; FALSE) |
Mencari teks yang mengandung an. |
=HLOOKUP("d*"; B1:F2; 2; FALSE) |
Mencari teks yang diawali dengan huruf d. |
=HLOOKUP("?ir*"; B1:F2; 2; FALSE) |
Mencari teks yang memiliki satu huruf di depan teks ir. |
=HLOOKUP("*an?"; B1:F2; 2; FALSE) |
Mencari teks yang memiliki satu huruf setelah an. |
=HLOOKUP("*~?*"; B1:F2; 2; FALSE) |
Mencari teks yang memiliki tanda tanya. |
=HLOOKUP("*~**"; B1:F2; 2; FALSE) |
Mencari teks yang memiliki tanda asterisk. |
Perhatikan rumus-rumus di atas. Contohnya rumus pertama. Ada tiga kandidat yang cocok berdasarkan kriteria wildcard, yaitu A*rian, Bertrand, Daniel, dan Firman. HLOOKUP menemuka A*rian terlebih dahulu sehingga nilai 1603000000 yang dikembalikan.
Kenapa Rumus HLOOKUP Tidak Berfungsi?
Saat menggunakan rumus HLOOKUP, mungkin teman-teman ketemu error atau mendapatkan hasil gak sesuai harapan.
Coba periksa kemungkinannya.
1. Nilai yang dicari harus berada di bagian teratas array.
Misalkan teman-teman mau HLOOKUP mengembalikan nama berdasarkan nomor seluler di array A1:J2, pastikan nomor-nomor seluler berada di cell-cell A1, B1, C1, dan seterusnya sampai J1.
2. Pastikan row_index_num mewakili posisi baris nilai kembalian di dalam array.
Mengacu pada pencarian berdasarkan nomor seluler di atas, maka nama-nama pemilik nomor adalah di baris 2.
Bila kita tentukan row_index_num sebagai 3, 4, -1, atau angka lainnya, HLOOKUP akan mengembalikan hasil tidak sesuai atau pesan error #REF!.
3. Approximate Match vs Exact Match.
Seperti contoh sebelumnya, Taman Safari dan Taman Safaro memberikan hasil yang sama bila kita menggunakan Approximate Match. Sebaliknya, hasil akan berbeda saat kita gunakan Exact Match.
4. Referensi tabel array berubah saat kita salin rumus ke cell-cell lain.
Untuk menghindari referensi tabel array berubah saat kita salin rumus ke cell-cell lain, tambahkan tanda absolut ke referensi atau gunakan nama tabel agar lebih praktis.
Bila memang dimaksudkan berubah, hilangkan referensi absolut.
5. Menambahkan atau menghapus baris array.
Saat kita menambah atau menghapus baris array, periksa kembali rumus. Perhatikan apakah nilai-nilai yang dicari masih berada di baris teratas atau apakah nilai-nilai kembalian masih sesuai dengan row_index_num yang kita tetapkan.
6. Duplikat nilai di dalam array.
HLOOKUP mengembalikan nilai pertama yang ditemukan.
Sebagai contoh, bila ada dua nama Joni masing-masing di cell H1 dan Q1, maka HLOOKUP akan mengembalikan nilai dari baris pada kolom H, bukan Q.
7. Tipe Data lookup_value Berbeda dengan Baris Pertama Array.
Ingat. Excel punya tipe data. Angka 5 yang kita lihat bisa jadi berbeda antara satu cell dengan cell lainnya.
Pastikan lookup_value punya tipe data yang sama dengan baris pertama array.
8. Karakter Selipan.
Periksa apakah ada karakter tidak terlihat terselip di dalam nilai lookup_value atau baris teratas array, seperti spasi.