HLOOKUP searches the first row of a table and returns a value from a chosen row in the matching column. Set the final argument to FALSE for exact matching.
HLOOKUP function syntax
=HLOOKUP(search_key, range, index, [is_sorted])- search_key: value in the top row.
- range: table including lookup keys.
- index: row position within that range, starting at one.
- is_sorted: FALSE for exact; TRUE or omitted for approximate matching on ascending keys.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Region | Q1 | Q2 | Q3 |
|---|---|---|---|
| North | 100 | 150 | 200 |
| South | 80 | 120 | 170 |
| East | 90 | 140 | 190 |
Look up a quarter exactly
Enter this formula in A10. Q2 is in the first row of the lookup range. Row index two selects the North row, giving 150.
=HLOOKUP("Q2",B1:D4,2,FALSE)Result: 150.
Choose a deeper result row
Index three selects South. This is a position within B1:D4, including its heading row, not a separate range argument.
=HLOOKUP("Q2",B1:D4,3,FALSE)Result: 120.
Use sorted tier boundaries
Approximate matching chooses the largest boundary no greater than seventy-five. The boundaries must be in ascending order.
=HLOOKUP(75,{0,50,100;"Low","Medium","High"},2,TRUE)Result: Medium.
Look up both a row and a column
MATCH includes the Region header so its returned position aligns with HLOOKUP’s range. Omitting that header would shift the row index.
=HLOOKUP("Q3",B1:D4,MATCH("South",A1:A4,0),FALSE)Result: 170.
Handle a missing exact key
No Q4 column exists in this dataset. IFNA catches a missing match while leaving other kinds of error visible.
=IFNA(HLOOKUP("Q4",B1:D4,2,FALSE),"Not found")Result: Not found.
Other Google Sheets articles you may also like