Excel XLOOKUP Function: Syntax, Examples and How It Beats VLOOKUP
Searches any range and returns a value from any other, in either direction, with a built-in fallback.
XLOOKUP is the lookup function Excel should always have had. Instead of one range that has to contain both the thing you search and the thing you return, it takes them separately: here is the column to search, here is the column to return. That one change removes almost every VLOOKUP limitation at once.
It matches exactly by default, so the silent wrong-answer bug that catches VLOOKUP users cannot happen. It can return a column to the left of the one it searched. It takes a not-found value as an argument, so you rarely need IFERROR. And because you name the return range directly, inserting a column into the middle of your data does not quietly change what the formula returns.
The catch is availability: XLOOKUP exists in Microsoft 365 and Excel 2021 onwards. A workbook that has to open in Excel 2019 or earlier still needs VLOOKUP or INDEX+MATCH.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Arguments
lookup_valueRequired- The value to search for.
lookup_arrayRequired- The range to search in. A single column or row — not the whole table.
return_arrayRequired- The range to return from. It must be the same height as lookup_array, but it can sit anywhere relative to it, including to the left.
if_not_foundOptional- What to return instead of #N/A when there is no match. This is the argument that makes IFERROR unnecessary.
match_modeOptional- 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match with * and ?.
search_modeOptional- 1 first to last (the default), -1 last to first, 2 and -2 binary search on sorted data.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Stock | Price |
| 2 | Cordless drill | Power tools | 24 | 89.99 |
| 3 | Extension lead | Electrical | 140 | 12.5 |
| 4 | Safety goggles | Safety | 68 | 7.25 |
| 5 | Work gloves | Safety | 210 | 5.4 |
Worked examples
=XLOOKUP("Safety goggles", A2:A5, D2:D5)Result: 7.25
Search column A, return the matching row of column D. No column counting, no fourth argument for exact match.
=XLOOKUP(7.25, D2:D5, A2:A5)Result: Safety goggles
The reverse lookup VLOOKUP cannot do: searching column D and returning column A, which is to its left.
=XLOOKUP("Hammer", A2:A5, D2:D5, "Not stocked")Result: Not stocked
The fourth argument handles the miss directly, so there is no #N/A and no IFERROR wrapper.
=XLOOKUP("Safety", B2:B5, A2:A5, "None", 0, -1)Result: Work gloves
Two rows match Safety. search_mode -1 searches bottom to top, so this returns the last match rather than the first.
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: No match, and no if_not_found argument was supplied. The same text-versus-number and trailing-space mismatches that break VLOOKUP break XLOOKUP too.
How to fix it: Supply the fourth argument with something meaningful, and check both sides are the same data type.
#VALUE!
Why it happens: lookup_array and return_array are different sizes — a common result of selecting one range to the bottom of the data and the other to the bottom of the sheet.
How to fix it: Make both ranges exactly the same height. Converting the data to a Table and using structured references avoids this entirely.
#NAME?
Why it happens: The Excel version in use does not have XLOOKUP, so the name means nothing to it.
How to fix it: XLOOKUP needs Microsoft 365 or Excel 2021+. For older versions use INDEX+MATCH, which does the same job and works everywhere.
Tips worth knowing
- XLOOKUP can return a whole row or column, not just one cell — give it a multi-column return_array and it spills the entire matching record.
- Nest two XLOOKUPs, one for the row and one for the column, to do a two-way lookup without INDEX.
- match_mode 2 turns on wildcards: "Safety*" matches anything starting with Safety.
- Use search_mode -1 to get the most recent entry when a log has several rows for the same key.
Frequently asked questions
Is XLOOKUP faster than VLOOKUP?
On typical spreadsheets the difference is not noticeable. XLOOKUP scans only the two ranges you name rather than the whole table, which helps on very wide data, but you should choose it for correctness and readability rather than speed.
Why does XLOOKUP return #NAME? on my colleague's computer?
Their Excel is older than 2021. XLOOKUP is not available there and the file will show #NAME? wherever you used it. If the workbook has to be shared with older versions, use INDEX+MATCH instead.
Can XLOOKUP return more than one value?
It returns one match, but that match can be an entire row or column: pass a return_array several columns wide and the whole record spills into the neighbouring cells. To return every row that matches a condition rather than just one, use FILTER.
Related functions
- VLOOKUPFinds a value in the first column of a table and returns something from a column to its right.
- INDEX MATCHMATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.
- FILTERReturns every row of a range that meets your condition, as a live result that resizes itself.
Guides that use it
Longer reads where this function does real work in a real sheet.
- Duplicates: The Payment Run Came to 39,488.00 and the Invoices Came to 23,081.75
- Multi-Currency Workbooks: January Was 132,549.03 in February and 134,880.05 in March, and Not One Invoice Changed
- Two Lists That Should Agree: Comparing Columns, COUNTIF, MATCH and the 490.60 That Was Three Problems
- INDEX and MATCH: The Lookup Pair That Survives a Moved Column