HLOOKUP vs XLOOKUP: Horizontal Lookups Compared

The short answer to HLOOKUP vs XLOOKUP: both find a value in a table that runs sideways, with labels across the top instead of down the side. HLOOKUP searches only the top row and needs you to count which row to return. XLOOKUP searches any row, returns from any row and matches exactly unless you tell it otherwise. This guide is for anyone who meets horizontal rate cards, monthly budgets or price grids at work or in an Excel test.

HLOOKUP vs XLOOKUP at a glance#

XLOOKUP can do everything HLOOKUP does, with fewer ways to go wrong, but it needs a newer version of Excel.

How the syntax differs#

HLOOKUP takes one block of cells and a row number. XLOOKUP takes two separate rows: one to search and one to return.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value is what you want to find, such as a product code.
• table_array (HLOOKUP) is the whole table. HLOOKUP always searches its first row.
• row_index_num (HLOOKUP) is the row to return, counted from the top of the table. The top row is 1.
• range_lookup (HLOOKUP) is FALSE for an exact match. If you leave it out, HLOOKUP uses TRUE, an approximate match.
• lookup_array and return_array (XLOOKUP) are two rows of the same width. XLOOKUP finds the value in the first and returns the cell in the same column of the second.

HLOOKUP is the sideways twin of VLOOKUP, so most of the points in VLOOKUP vs XLOOKUP apply here too, turned on their side.

The same lookup, side by side#

Here is a small product card where each product is a column:

Task 1: find the price of P-300#

Both formulas return 32:

=HLOOKUP("P-300",A1:E4,3,FALSE)
=XLOOKUP("P-300",B1:E1,B3:E3)

In HLOOKUP, the 3 means "the third row of A1:E4", which is Price. In XLOOKUP, you point straight at the Price row, B3:E3, so there is nothing to count.

Task 2: find the code for the Lamp#

Now the item name is known and the code is wanted. The code is in row 1, above the row you search. HLOOKUP can't do this: it only searches the top row and only returns rows below it. XLOOKUP doesn't care about direction:

=XLOOKUP("Lamp",B2:E2,B1:E1)

The result is P-300. In older Excel, the same job needs INDEX and MATCH: =INDEX(B1:E1,MATCH("Lamp",B2:E2,0)). The comparison of INDEX MATCH vs XLOOKUP explains that pattern.

Task 3: a code that doesn't exist#

Search for P-500 and HLOOKUP returns #N/A. XLOOKUP lets you write the message in the fourth argument:

=XLOOKUP("P-500",B1:E1,B3:E3,"No such code")

The result is the text No such code. With HLOOKUP, wrap it in IFNA to get the same effect: =IFNA(HLOOKUP("P-500",A1:E4,3,FALSE),"No such code").

Task 4: return a whole column#

XLOOKUP can return more than one cell. Give it a return range several rows deep:

=XLOOKUP("P-200",B1:E1,B2:E4)

This spills three results down the column: Chair, 85 and 40. HLOOKUP returns one cell per formula, so you would need three formulas with row numbers 2, 3 and 4.

Approximate matches: tiers and bands#

Both functions can find the right band in a tier table, but HLOOKUP needs the top row sorted from smallest to largest. Here is a discount table:

For an order of 650, both return 10%:

=HLOOKUP(650,B7:E8,2,TRUE)
=XLOOKUP(650,B7:E7,B8:E8,,-1)

HLOOKUP with TRUE finds the largest value that is less than or equal to 650, which is 500. XLOOKUP's match_mode -1 means "exact match, or the next smaller item". The empty space between the commas skips the if_not_found argument. XLOOKUP also has 1 for "the next larger item", which HLOOKUP can't do.

When to use HLOOKUP#

Use HLOOKUP when the file must work everywhere, or when a task asks for it by name. It is a fine choice when:

• Colleagues use Excel 2019 or older. XLOOKUP shows #NAME? in those versions.
• A test asks for HLOOKUP. Some job tests still name it, and a grader may check the function you used.
• The table is stable. If nobody inserts rows, a fixed row number is safe.
• You only ever read rows below the header. That is exactly what HLOOKUP was built for.

Always finish an exact-match HLOOKUP with FALSE. Without it, a lookup on unsorted codes can quietly return the wrong column's value.

When to use XLOOKUP#

Use XLOOKUP for new work whenever everyone has Excel 2021, Excel 2024 or Microsoft 365. It is the better choice when:

• The answer is above the row you search, as in Task 2.
• Rows may be added later. XLOOKUP's ranges move when rows are inserted, so the formula keeps pointing at Price.
• You want a clean message instead of #N/A, without wrapping the formula in IFNA.
• The tier row isn't sorted, or you need "the next larger" band.
• The table lives on another tab. See how to use XLOOKUP from another sheet.

The complete guide to XLOOKUP covers every argument, and the XLOOKUP function guide is a quick syntax reference. If you need every matching value rather than the first one, look at XLOOKUP vs FILTER.

Common mistakes with HLOOKUP and XLOOKUP#

These slips cause most wrong or broken horizontal lookups:

1. Leaving out FALSE in HLOOKUP. The default is an approximate match, which can return a neighbour's value with no error.
2. Counting the row number from row 1 of the sheet. HLOOKUP counts from the top row of the table. If the table starts in row 5, row 5 is 1.
3. Inserting a row inside the table. Add a "Colour" row between Item and Price and =HLOOKUP("P-300",A1:E5,3,FALSE) now returns the colour. XLOOKUP's B3:E3 becomes B4:E4 by itself.
4. XLOOKUP rows of different widths. =XLOOKUP("P-300",B1:E1,B3:D3) returns #VALUE!, because the two rows must cover the same columns.
5. Not locking ranges before filling across. Press F4 on Windows or ⌘T on a Mac to add $ signs, then fill right with Ctrl+R (⌘R on a Mac).
6. Codes stored as text on one side and numbers on the other. The value 100 doesn't match the text "100" in either function.

Our recommendation#

Learn XLOOKUP as your default lookup in any direction. It replaces HLOOKUP and VLOOKUP with one function, it is exact by default, it can return from a row above, and inserted rows don't break it. Keep HLOOKUP in your toolkit for older files and for tests that name it, and always end it with FALSE when you want an exact match.

In short: XLOOKUP for new work, HLOOKUP with FALSE for older Excel.

Practise it#

Try the exercise on this page: it asks for the one lookup HLOOKUP can't do. Then practise HLOOKUP on a sideways rate card and practise XLOOKUP in a live spreadsheet, where you type each formula and it is checked instantly.