Syntax
HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
| Parameter | Description |
|---|---|
| lookup_value | Parameter of the HLOOKUP function. |
| table_array | Parameter of the HLOOKUP function. |
| row_index | Parameter of the HLOOKUP function. |
| [range_lookup] | (Optional.) Parameter of the HLOOKUP function. |
Examples
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | State | CA | NY | TX | FL |
| 2 | Tax Rate | 7.25% | 8.00% | 6.25% | 6.00% |
| 3 | Shipping | $8.50 | $9.00 | $7.50 | $7.00 |
Look up a tax rate from a rate table
Formula
=HLOOKUP("CA", A1:E2, 2, FALSE)
Returns: 0.0725 — finds "CA" in the header row and returns the tax rate from row 2
Get monthly data by month name
Formula
=HLOOKUP("March", A1:M2, 2, FALSE)
Returns: 45000 — looks up March in the header row and returns the sales figure below it
Approximate match in sorted headers
Formula
=HLOOKUP(750, A1:D3, 3, TRUE)
Returns: the value from row 3 under the column with the largest header value <= 750
Common Errors
#N/A
The lookup value was not found in the first row. Check spelling and use FALSE for the last argument to require an exact match.
#REF!
The row_index_num exceeds the number of rows in the table. For example, requesting row 5 from a 3-row table.
Tips
Use FALSE for exact match
The default (TRUE) does approximate matching on sorted data. Almost always use FALSE for exact matching.
HLOOKUP vs INDEX-MATCH
INDEX-MATCH is more flexible. =INDEX(row_range, MATCH(lookup, header_range, 0)) works the same way without the row number limitation.
When to use HLOOKUP
Use HLOOKUP when your data is arranged horizontally (categories across columns). For vertical data, use VLOOKUP or INDEX-MATCH.
Try HLOOKUP in Viztab
Import your data and use HLOOKUP with 370+ other formulas. No signup required.
Open Viztab