Home › Excel functions › HLOOKUP
HLOOKUP in Excel: Syntax, Examples and Alternatives
HLOOKUP is the horizontal version of VLOOKUP. It searches the top row of a table for a value and returns the value from a row below it. It's useful when your data runs across columns, like months or quarters.
HLOOKUP · In this guide
HLOOKUP syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])| Argument | What it means |
|---|---|
| lookup_value | The value to find in the first row, such as "Q3". |
| table_array | The table to search. The lookup value must be in its top row. |
| row_index_num | Which row of the table to return. The top row is 1. |
| range_lookup (optional) | FALSE for an exact match. Leave it out and Excel uses an approximate match. |
Step-by-step example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quarter | Q1 | Q2 | Q3 | Q4 |
| 2 | Sales | 12,000 | 15,500 | 14,200 | 18,300 |
| 3 | Costs | 8,000 | 9,100 | 9,400 | 10,200 |
Excel searches the top row B1:E1 for "Q3", finds it in column D, and goes down to the 2nd row of the table: 14,200. Change the 2 to a 3 and you get the costs: 9,400.
Row numbers count from the top of your range, not from row 1 of the sheet. If your table starts in row 5, then row 5 is 1.
Exact vs. approximate match
Just like VLOOKUP: use FALSE for names, codes and labels. Use TRUE only for ranges like rate tiers, and then the top row must be sorted from smallest to largest, left to right. If you leave the last argument out, Excel uses an approximate match, which can silently return a wrong value.
The modern alternative: XLOOKUP
XLOOKUP works horizontally too, without counting rows:
=XLOOKUP("Q3", B1:E1, B2:E2)It's exact by default, doesn't break when you insert rows, and can return a value from a row above the lookup row. It's available in Microsoft 365, Excel 2021 and 2024. In older versions, INDEX/MATCH does the same: =INDEX(B2:E2, MATCH("Q3", B1:E1, 0)).
Common errors
- #N/A: the value isn't in the top row, or has extra spaces. Check with TRIM.
- #REF!: the row number is bigger than the number of rows in the table.
- #VALUE!: the row number is less than 1.
- Wrong result: you left out FALSE, or counted the row number from the top of the sheet instead of the table.
Frequently asked questions
What's the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down the first column of a table and returns a value from a column to the right. HLOOKUP searches across the first row and returns a value from a row below.
Can HLOOKUP look up a value above the lookup row?
No. HLOOKUP can only return rows below the first row of the table. Use XLOOKUP or INDEX/MATCH for that.
Should I still use HLOOKUP?
It works fine, but XLOOKUP is easier and more robust if you have a recent version of Excel.