Excel functions / Lookup & reference
HLOOKUP
Finds a value in the top row of a table and returns a value from a row below it.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Arguments
lookup_value | What you are searching for in the top row. |
|---|---|
table_array | The table to search. HLOOKUP looks across its first row. |
row_index_num | Which row to return, counting the top row as 1. |
range_lookup | FALSE for an exact match. |
How it works
HLOOKUP is VLOOKUP turned on its side. The H stands for horizontal: it searches across the top row, then counts down to the row you ask for.
Use it when your headings run along the top and you want a value from beneath one of them, such as a month in a monthly report.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | 3 | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: 3. Finds the Stock heading and returns the value 3 rows down: the stock for Desk.
Tips
- Most tables are laid out in columns, so you will use VLOOKUP or XLOOKUP far more often.
- XLOOKUP can search across a row too, and it is easier to read.
Practise HLOOKUP
Try it yourself in a real spreadsheet, with instant feedback on every answer.