Quick Navigation
HLOOKUP Function
Summary
The HLOOKUP function performs horizontal lookups by searching for a specified value in the top row of a table and returning a value from the same column in a designated row below. Perfect for horizontally organized data structures like category headers across the top with related data underneath.
Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| lookup_value | Any |
Yes | Value to search for in the first row of the table (number, text, or cell reference) |
| table_array | Range |
Yes | Table range where first row contains lookup values and subsequent rows contain return data |
| row_index_num | Number |
Yes | Row number (1-based) in table_array from which to retrieve the value |
| range_lookup | Boolean |
No | TRUE for approximate match (default), FALSE for exact match |
Using the HLOOKUP Function
HLOOKUP excels at retrieving data from horizontally structured tables where category names or keys appear across the top row. Use it for sales data by product category, pricing tables with headers, or any dataset where you need to match a top-row identifier and pull corresponding row data.
Common HLOOKUP Examples
Basic Product Lookup
=HLOOKUP("Axles", A1:C4, 2, TRUE)Searches for 'Axles' in row 1 and returns value from row 2 (result: 4)
Exact Match Category Lookup
=HLOOKUP("Bearings", A1:C4, 3, FALSE)Finds exact 'Bearings' match and returns row 3 value (result: 7)
Approximate Match Example
=HLOOKUP("B", A1:C4, 3, TRUE)No exact 'B' match found, uses largest value less than 'B' (Axles), returns row 3 value (result: 5)
Array Formula Lookup
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)Looks up 3 in array, returns row 2 value from same column (result: 'c')
Frequently Asked Questions
Common Errors and Solutions
#N/A
Cause: lookup_value not found (exact match) or smaller than all top-row values
Solution: Verify lookup_value exists or use approximate match with sorted data
#REF!
Cause: row_index_num exceeds table_array row count
Solution: Use valid row number ≤ table height
#VALUE!
Cause: row_index_num < 1
Solution: Row index must be 1 or greater
Notes
- Consider XLOOKUP as modern replacement - more flexible and exact by default
- For approximate matches, first row MUST be sorted ascending left-to-right
- Case-insensitive text matching
- Use ~ before ? or * for literal wildcard characters
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+