HLOOKUP function
HLOOKUP searches across the top row of a table for a match and returns a value from a row below it. It is the sideways twin of VLOOKUP, now mostly replaced by XLOOKUP.
Example
Read it as a sentence: look for Qty across the top row of B1:E7, go down to the 3rd row, and accept only an exact match, which gives 7.
Excel shows 7
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments in [square brackets] are optional.
-
lookup_value(required) - Value to find in the top row.
-
table_array(required) - The table, with the lookup row at the top.
-
row_index_num(required) - Row of the table to return from, counting the top row as 1.
-
range_lookup(optional) - FALSE for exact match; TRUE or omitted for approximate (sorted ascending).
Returns: The matching value from the chosen row.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=HLOOKUP(15,{10,20,30;"a","b","c"},2) | a | Approximate |
=HLOOKUP(9.5,{0,1,5,50;"Walk","Short cab","Long cab","Train"},2,TRUE) | Long cab | Site: approximate match into bands |
=HLOOKUP("Qty",Items!B1:E7,3,FALSE) | 7 | Exact |
=HLOOKUP("qty",Items!B1:E7,3,FALSE) | 7 | Case-insensitive |
=HLOOKUP("Qt*",Items!B1:E7,2,FALSE) | 12 | Wildcard |
=HLOOKUP("Qty",Items!B1:E7,"3",FALSE) | 7 | Text row index |
=HLOOKUP(5,{10,20,30;"a","b","c"},2,TRUE) | #N/A | Below first |
=HLOOKUP("Zither",Items!B1:E7,2,FALSE) | #N/A | Not found |
The Items sheet these formulas read
B
C
D
E
1
Item
Category
Qty
Unit Value
2
Boxer cartridge
Tools
12
$3.50
3
Briar pipe
Smoking
7
$4.25
4
Candle
Household
20
$2.00
5
Cigar
Smoking
5
$10.00
6
Clay pipe
Smoking
15
$1.75
7
Commonplace book
Papers
9
$6.00
Items
Watch out for
- Omitting the last argument uses approximate match, which can return wrong answers on unsorted data; use FALSE.
- Row index is fixed, so inserting rows can break it.
- Cannot look upward.
Where it works
- Excel
- HLOOKUP is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .