Excel HLOOKUP Function: Look Up Across a Row Instead of Down a Column
Searches the top row of a table and returns a value from a row beneath it.
HLOOKUP is VLOOKUP rotated ninety degrees. Where VLOOKUP searches down the first column of a range, HLOOKUP searches across the first row — the H is for horizontal — and returns a value from a row below it. Everything else about the two is identical, including the argument order and the fourth-argument trap.
It exists because some tables genuinely are laid out sideways. A quarterly summary with Q1 to Q4 across the top and metrics down the side is a natural shape for a report, and looking up "Q3" in it needs HLOOKUP rather than VLOOKUP.
It is worth being blunt: if your Excel has XLOOKUP, use that instead. XLOOKUP handles both orientations with the same syntax, matches exactly by default, and does not break when someone inserts a row. HLOOKUP is here because you will meet it in other people's files and because Excel 2019 and earlier have nothing better for a horizontal table.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Arguments
lookup_valueRequired- The value to search for. It is matched against the first row of table_array and nothing else.
table_arrayRequired- The range to search. Its top row must contain lookup_value. Lock it with absolute references before filling across.
row_index_numRequired- Which row of table_array to return, counted from 1 at its top edge — not from row 1 of the sheet.
range_lookupOptional- FALSE for an exact match, TRUE for approximate. It defaults to TRUE, which is almost never what you want. Always pass FALSE.
The example data
A horizontally laid-out table: labels across row 1, data in row 2 and row 3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Metric | Q1 | Q2 | Q3 | Q4 |
| 2 | Revenue | 48000 | 52500 | 61000 | 74500 |
| 3 | Headcount | 12 | 12 | 14 | 17 |
Worked examples
=HLOOKUP("Q3", A1:E3, 2, FALSE)Result: 61000
Finds Q3 in the top row and returns the second row of the range — the revenue figure.
=HLOOKUP("Q3", A1:E3, 3, FALSE)Result: 14
Same search, third row. Counting starts at the top edge of the range, not at sheet row 1.
=HLOOKUP("Q4", $A$1:$E$3, 2, FALSE) - HLOOKUP("Q1", $A$1:$E$3, 2, FALSE)Result: 26500
Two lookups compared. The $ signs let the formula be copied without the table drifting.
=XLOOKUP("Q3", B1:E1, B2:E2)Result: 61000
The modern replacement. No row counting, exact match by default, and it reads far better.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: The value is not in the top row, or is there in a form that does not match — a trailing space, or a number stored as text.
How to fix it: TRIM the lookup value and check both sides are the same data type. Wrap in IFERROR if a miss is expected.
#REF!
Why it happens: row_index_num is larger than the number of rows in table_array — asking for row 4 of a three-row range.
How to fix it: Count rows from the top of your range. This often appears after someone deletes a row inside the table.
Wrong value returned, no error
Why it happens: range_lookup was omitted, so it defaulted to TRUE and returned an approximate match.
How to fix it: Always pass FALSE. This is the same silent bug VLOOKUP has, and it is just as easy to miss.
Breaks after inserting a row
Why it happens: row_index_num is a hard-coded number that does not update when the table's shape changes.
How to fix it: Use XLOOKUP, or replace the number with MATCH on the label column.
Tips worth knowing
- HLOOKUP cannot look upward, exactly as VLOOKUP cannot look left. The return row must be below the search row.
- Lock the table with absolute references before filling the formula across a row.
- Replace the hard-coded row number with MATCH so it survives rows being inserted.
- If your data is horizontal and you find yourself fighting HLOOKUP, transposing the table is often the better fix.
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
Only the direction. VLOOKUP searches down the first column of a range and returns from a column to the right; HLOOKUP searches across the first row and returns from a row below. The arguments, the defaults and the errors are otherwise the same.
Should I use HLOOKUP or XLOOKUP?
XLOOKUP, if your version has it. It handles horizontal and vertical lookups with the same syntax, defaults to an exact match, survives inserted rows, and takes a not-found value directly. HLOOKUP is for Excel 2019 and earlier, and for reading files other people wrote.
Why does my HLOOKUP return #N/A on a value I can see?
Almost always a formatting mismatch rather than a missing value — a trailing space in one of the cells, or a number stored as text on one side. Test with =A1=B1: if two cells that look identical return FALSE, that is your answer.
Related functions
- VLOOKUPFinds a value in the first column of a table and returns something from a column to its right.
- XLOOKUPSearches any range and returns a value from any other, in either direction, with a built-in fallback.
- INDEX MATCHMATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.
Guides that use it
Longer reads where this function does real work in a real sheet.