The HLOOKUP function is used to search for a value in the first row of a table and return a corresponding value from a specified row in the same column.
HLOOKUP stands for Horizontal Lookup.
It is useful when data is arranged horizontally (in rows instead of columns).
Structure of HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments:
- lookup_value → The value to search in the first row
- table_array → The data table range
- row_index_num → Row number to return result from
- range_lookup → TRUE (Approximate) or FALSE (Exact match)
Example 1: Exact Match
If:
A1:E3 contains data
A1:E1 = Product Names
A2:E2 = Prices
A3:E3 = Quantity
Formula:
=HLOOKUP("Laptop", A1:E3, 2, FALSE)
This searches “Laptop” in the first row and returns the value from the 2nd row (Price).
Example 2: Approximate Match
=HLOOKUP(A1, A1:E3, 3, TRUE)
Used in grading systems or commission tables.
When using TRUE, the first row must be sorted in ascending order.
Important Rules
- HLOOKUP searches horizontally (left to right).
- Lookup value must be in the first row of the table.
- Row index number starts from 1.
- FALSE is recommended for exact matches.
Common Errors
- #N/A → Value not found
- #REF! → Row number is incorrect
- #VALUE! → Wrong formula format
Handling Errors
=IFERROR(HLOOKUP(A1, A1:E3, 2, FALSE), "Not Found")
This prevents displaying errors.
Difference Between VLOOKUP and HLOOKUP
- VLOOKUP → Searches vertically (columns)
- HLOOKUP → Searches horizontally (rows)
Both functions are widely used for retrieving data quickly.
Why HLOOKUP is Important
It helps in structured reports where data is arranged horizontally, such as:
- Monthly sales reports
- Exam result sheets
- Product comparison tables
HLOOKUP is useful when working with row-based datasets in Excel.