HLOOKUP
Look a value up along the first row of a block.
Read a value off a table laid out in rows rather than columns
The value in the row you asked for, from the column that matched
Syntax
=HLOOKUP(lookup_value, table, row_number, [exact_match])
| Argument | Required | What goes here |
|---|---|---|
lookup_value | Required | The value to find. |
table | Required | The block to search. Its first row is the one searched. |
row_number | Required | Which row of that block to read back, counting the first as 1. |
exact_match | Optional | FALSE for an exact match. |
How to use HLOOKUP
HLOOKUP is VLOOKUP turned on its side. It searches the first row of a block and reads down to the row number you name.
It exists because some reports are laid out with periods along the top rather than down the side, which is common in a budget.
Everything true of VLOOKUP is true here: exact matching needs FALSE, the row number counts inside the block, and inserting a row breaks it silently.
In practice it turns up far less often than VLOOKUP, and mostly as the wrong answer beside it in a list of choices. If the data really is horizontal, XLOOKUP handles both directions without a separate function.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=HLOOKUP("Amount",A1:E9,4,FALSE)Find the Amount heading along row 1, then read down to the fourth row of the block. | 2450 |
=HLOOKUP("Days overdue",A1:E9,4,FALSE)The same row, a different column, by changing only what is being searched for. | 63 |
=HLOOKUP("Status",A1:E9,2,FALSE)Row 2 of the block is the first row of data. | Overdue |
Notes
- The row number counts from the top of the block, so the heading row is row 1 and the first data row is row 2.
- XLOOKUP searches a row or a column with the same arguments, which is why HLOOKUP is rarely the right choice in a current version of Excel.