Revenue for a given month
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Revenue | 12000 | 13500 | 15200 |
| 3 | Costs | 8000 | 8200 | 9100 |
Formula: F1: =HLOOKUP("Feb",B1:D3,2,FALSE)
Result: 13500
Finds Feb in the top row of B1:D3 and returns the 2nd row of the table (Revenue).
Lookup & reference
HLOOKUP
Same, but searches the first row.
Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Example: =HLOOKUP("March",A1:M5,3,0)
Available in: All versions
HLOOKUP searches the top row of a table for a value and returns a value from a row below it, in the same column. It is the horizontal version of VLOOKUP, built for data laid out with headers across the top.
When to use it
- Your lookup keys are in a row (months, years, product codes across columns) rather than a column.
- You are pulling a figure from a report whose layout you cannot rotate.
- You need a quick tier lookup from a horizontal rate table.
- In Microsoft 365, consider XLOOKUP, which works in both directions with one syntax.
Arguments explained
- lookup_value. The value to find in the top row of the table, for example a month name used as a column header.
- table_array. The range whose first row is searched. Rows below it hold the results.
- row_index_num. Which row of table_array to return, counted from 1 for the top row. Row 2 is the row directly under the headers.
- range_lookup. FALSE for an exact match, which is what you normally need. TRUE or omitted finds an approximate match and requires the top row to be sorted ascending.
Worked examples
Costs for March
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Revenue | 12000 | 13500 | 15200 |
| 3 | Costs | 8000 | 8200 | 9100 |
Formula: F1: =HLOOKUP("Mar",B1:D3,3,FALSE)
Result: 9100
Row index 3 is the Costs row of the table.
Horizontal tier table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Orders from | 0 | 50 | 200 |
| 2 | Discount | 0 | 0.05 | 0.1 |
Formula: A4: =HLOOKUP(120,B1:D2,2,TRUE)
Result: 0.05
120 lies between 50 and 200, so approximate match returns the 50-tier discount of 5%.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in G2: =HLOOKUP("Q3",B1:E3,2,FALSE)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Metric | Q1 | Q2 | Q3 | Q4 | Result | |
| 2 | Revenue | 120 | 135 | 150 | 170 | 150 | |
| 3 | Costs | 80 | 85 | 90 | 100 |
Result in G2: 150
Common errors and how to fix them
- #N/A. The lookup value is not in the top row, or it differs by spaces or data type. Use FALSE for exact matching.
- #REF!. The row index is larger than the number of rows in the table.
- Wrong value with TRUE. Omitting the last argument means approximate match, which requires the top row sorted ascending.
- Reference moves when copied. Lock the table with $B$1:$D$3 before filling across.
Related functions
Frequently asked questions
- What is the difference between VLOOKUP and HLOOKUP?
- VLOOKUP searches the first column and returns from a column; HLOOKUP searches the first row and returns from a row.
- Can HLOOKUP return rows above the header?
- No, it only returns rows below the lookup row. Use INDEX/MATCH or XLOOKUP for that.
- Is HLOOKUP still worth learning?
- It is common in older workbooks, so yes, but for new files XLOOKUP is simpler.
- Does HLOOKUP support wildcards?
- Yes, with exact match (FALSE): "Q*" matches the first header starting with Q.