The HLOOKUP function in Excel
HLOOKUP (horizontal lookup) is VLOOKUP turned sideways. It searches the first row of a table for a value, then returns the value from the same column, in a row you choose. Use it when your headers run across the top, such as months or years, and your data sits in rows below them.
For new workbooks, XLOOKUP can do the same job and more. You will still meet HLOOKUP in older files and in reports built with a row-based layout.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
| Argument | What it means |
|---|---|
| lookup_value | What to look for in the first row, e.g. a month name. Usually a cell reference. |
| table_array | The table to search. The lookup value must be in its first row. |
| row_index_num | Which row of the table to return, counted from the table's first row (1 = first row). |
| range_lookupoptional | FALSE for an exact match. TRUE (the default) for an approximate match on a sorted first row. |
Examples
1. Read the actual sales for a month
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Measure | Jan | Feb | Mar | Month | Feb | |
| 2 | Target | 100 | 120 | 90 | Actual | 130 | |
| 3 | Actual | 95 | 130 | 88 |
HLOOKUP finds Feb in the first row of A1:D3, then moves down to the 3rd row (Actual) and returns 130. FALSE makes it an exact match.
2. Read the target for a month
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Measure | Jan | Feb | Mar | Target for Mar | |
| 2 | Target | 100 | 120 | 90 | 90 | |
| 3 | Actual | 95 | 130 | 88 |
Same table, but only the first two rows. Row 2 is Target, so the answer is the March target: 90.
3. Show a message when the month is missing
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Measure | Jan | Feb | Mar | Month | Apr | |
| 2 | Target | 100 | 120 | 90 | Actual | No data | |
| 3 | Actual | 95 | 130 | 88 |
April isn't in the header row, so HLOOKUP returns #N/A. IFERROR replaces the error with a clear message.
Common mistakes
Leaving out the 4th argument. Without FALSE, HLOOKUP may return a neighbouring column's value.
Fix: Add FALSE for exact matches:
=HLOOKUP(G1,A1:D3,3,FALSE).Counting the row number from the sheet's row 1. The number counts from the first row of the table.
Fix: If the table starts at row 5, the first row is 1 even though it sits in row 5.
Looking for the value in the wrong place. HLOOKUP only searches the top row of the table.
Fix: Put the value you search for in the first row of the table, or use XLOOKUP or INDEX with MATCH.
Mixing up rows and columns. A table with months across the top needs HLOOKUP; a table with product IDs down the side needs VLOOKUP.
Fix: Check where your lookup values sit: across the top means HLOOKUP, down the left means VLOOKUP.
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
VLOOKUP searches down the first column and returns a column number. HLOOKUP searches across the first row and returns a row number. Otherwise they work the same way.
Should I use HLOOKUP or XLOOKUP?
Use XLOOKUP when everyone opening the file has Excel 365 or Excel 2021 or newer. Use HLOOKUP when older versions must still open the file.
Why does HLOOKUP return #N/A?
The value isn't in the first row of the table, or it differs slightly (extra spaces, text instead of a number). Check both sides of the lookup.
Related functions
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.