HLOOKUP in Excel: how the horizontal lookup works

HLOOKUP looks for a value along the top row of a table, then returns whatever sits below it in that same column, a set number of rows down. Its syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): the value to find, the table to search, how many rows down to read the answer, and whether the match must be exact. So =HLOOKUP("Q3", A1:E3, 2, FALSE) finds Q3 along the top row and returns the value two rows down in its column.

The H is for horizontal, and that is the whole point. VLOOKUP scans the first column and returns a value to its right; HLOOKUP does the same job turned on its side, for data laid out in rows. That axis is the one thing to keep straight. If your labels run across the top of a block, HLOOKUP reads down from them; if they run down the side, you want VLOOKUP or XLOOKUP instead.

How does HLOOKUP work?

HLOOKUP takes four arguments, and only the last is optional:

• lookup_value is the value to find in the top row of the table. It can be a number, a cell reference, or text.
• table_array is the block of cells to search, given as a range or a range name. HLOOKUP always looks in its top row.
• row_index_num is which row of that block to pull the answer from, counted from the top. A 1 returns the top row itself, a 2 returns the row beneath it, and so on.
• range_lookup is whether an approximate match is allowed. Leave it off or set it to TRUE for approximate, or set it to FALSE for an exact match. This one is optional, and it causes the most trouble.

Here a small table holds quarterly figures with the quarter labels running across the top:

The quarter labels sit in row 1, so that is the row HLOOKUP searches. Asking for Q3 lands on column D, and row_index_num of 2 reads down to D2, the revenue for that quarter, 150. The returned value always sits in the column HLOOKUP matched and the row you numbered, which is why getting the row number right matters as much as finding the value.

Exact match or approximate? The range_lookup argument

Most of the time you want an exact match, which means writing FALSE as the fourth argument.

Approximate mode, TRUE or the argument left off, does not need an exact hit. When it can’t find one, it settles for the next largest value that is still less than the lookup value, which is only sensible when the top row is sorted ascending. On an unsorted top row, an approximate HLOOKUP can return a plausible wrong value instead of an error, so FALSE is the safer default for the everyday case of matching a label exactly. With FALSE and a text lookup value, the wildcards ? for a single character and * for any run of characters work too.

Why does HLOOKUP return #N/A, #REF!, or #VALUE!?

Three error codes cover almost every broken HLOOKUP, and each points at a different argument.

#N/A is the common one: the lookup value isn’t in the top row. With FALSE, that means no exact match was found, and the reason is usually small.

The same type and space mismatches that break a VLOOKUP break an HLOOKUP, and why your VLOOKUP returns #N/A walks through each fix in detail. There’s also an approximate-mode case: when the lookup value sits under the lowest entry in the top row, HLOOKUP returns #N/A, because there’s nothing at or below it to settle for.

#REF! comes from the row number, not the value. Ask for row_index_num of 5 in a table only three rows tall and HLOOKUP returns #REF!, because that row isn’t there.

#VALUE! is the mirror mistake: a row_index_num less than 1. Count the rows in your table_array and keep the number between 1 and that count. What every error code means, and when one is safe to ignore, is in Excel’s error values, explained.

One failure throws no error at all: pointing row_index_num at the wrong row. An off-by-one number returns a real value from the wrong line, and nothing about the cell looks broken. Check the row count the same way you’d check a boundary in an IF formula, by reading back what the number actually lands on.

HLOOKUP vs VLOOKUP, INDEX MATCH, and XLOOKUP

HLOOKUP is one of four ways to look a value up, and the choice is mostly about the layout of your data and the version of Excel you’re in.

• VLOOKUP is the vertical sibling: it searches the first column and returns a value to the right, for data laid out in columns. Microsoft’s own steer is to use HLOOKUP when your comparison values run across the top row and VLOOKUP when they run down a left column. The full comparison, including when to move to the newer function, is in VLOOKUP vs XLOOKUP.
• INDEX and MATCH look in any direction. Because MATCH can search along a row just as easily as down a column, INDEX and MATCH handle a horizontal lookup that also needs to return a value above the row it searched, which HLOOKUP can’t do.
• XLOOKUP is the modern replacement. Microsoft’s HLOOKUP page points you at it outright: “Try using the new XLOOKUP function, an improved version of HLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.” Which versions of Excel actually have it is covered in the XLOOKUP guide.

The short version

• The mechanic: HLOOKUP searches the top row of a table and returns a value from the row you number below it, in the same column. VLOOKUP is the same idea rotated for columns, covered in VLOOKUP vs XLOOKUP.
• The match: write FALSE for an exact match; TRUE or an omitted last argument does an approximate match that needs the top row sorted in ascending order.
• The errors: a value not in the top row returns #N/A; a row_index_num above the table’s row count gives #REF!, below 1 gives #VALUE!. The full catalog is in Excel errors explained.
• The alternatives: for a lookup that can also return upward or leftward, INDEX and MATCH work in any direction, and on a current Excel XLOOKUP replaces HLOOKUP and VLOOKUP both.