XLOOKUP in Excel: Syntax, Examples and Errors
Updated 2026-10-12 · Free · Works in Excel & Google Sheets
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Quick answer
XLOOKUP searches a range for a value and returns items from another range on the same row — looking left or right, with exact match by default and a built-in "not found" message: =XLOOKUP(A2, $D$2:$D$500, $E$2:$E$500, "Not found"). #NAME? almost always means your Excel is older than 2021; #N/A means the value is not in the lookup column, or only appears to be (text vs. number, invisible spaces).
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Required | What it means |
|---|---|---|
| lookup_value | Yes | The value to search for: a number, text, date or a cell reference like A2. |
| lookup_array | Yes | The column to search in. Unlike VLOOKUP, it does not have to be the first column of anything — you point at one column directly. |
| return_array | Yes | The column (or columns) to return from. It can sit to the LEFT of the lookup column, and both ranges must have the same number of rows. |
| if_not_found | No | A custom message or value to return instead of #N/A — no IFERROR wrapper needed. |
| match_mode | No | 0 (default) = exact match. -1 = exact or next smaller. 1 = exact or next larger. 2 = wildcard match with * and ?. |
| search_mode | No | 1 (default) = first to last. -1 = reverse search (last match first). 2/-2 = binary search on sorted data. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =XLOOKUP(A2; $D$2:$D$500; $E$2:$E$500; "Not found"). This is standard in countries where the comma is the decimal separator.
Basic examples
=XLOOKUP(A2, $D$2:$D$500, $E$2:$E$500, "Not found")The everyday pattern: find the value of A2 in column D and return the matching item from column E. If nothing matches, the cell shows "Not found" instead of a raw #N/A — no error wrapping required.
=XLOOKUP(A2, $E$2:$E$500, $D$2:$D$500, "Not found")A left-lookup: the return column sits to the left of the lookup column. This is the case that forced people into INDEX/MATCH before XLOOKUP existed.
=XLOOKUP(A2, $D$2:$D$500, $E$2:$E$500, "Not found", 0, -1)Search from last to first: with duplicate keys, this returns the most recent match instead of the first one — handy for price lists and status logs.
lookup_array and return_array must span exactly the same number of rows. If one range is D2:D500 and the other E2:E499, XLOOKUP fails with #VALUE!.
Common errors and how to fix them
| Error | Most common cause | Fix |
|---|---|---|
| #NAME? | Your Excel version predates XLOOKUP (Excel 2019 and older), or the function name is misspelled. | XLOOKUP needs Microsoft 365 or Excel 2021+. On older versions use INDEX/MATCH or VLOOKUP. |
| #VALUE! | lookup_array and return_array have different row counts, or a single cell was passed as one of the arrays. | Make both ranges exactly the same height, e.g. D2:D500 and E2:E500. |
| #N/A | The value is not in the lookup column — or it only looks identical (text vs. number, trailing or non-breaking spaces). | Convert text-numbers with VALUE() and clean invisible spaces; or add an if_not_found argument for a friendly message. |
| #SPILL! | A multi-column return_array cannot spill because a neighbouring cell is occupied. | Clear the cells below/right of the formula where results want to expand. |
Trailing spaces and non-breaking spaces copied from the web are the sneakiest #N/A cause here too: to XLOOKUP, "NYC " and "NYC" are different strings. Clean the whole column in one click before searching.
XLOOKUP vs. VLOOKUP
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Available in | Microsoft 365 and Excel 2021+ | Every Excel version (and Google Sheets) |
| Looks left of the key column | Yes | No |
| Exact match by default | Yes | No — you must add FALSE |
| Survives inserted columns | Yes — ranges point at whole columns | No — the fixed column index can shift |
| When the value is missing | Built-in if_not_found argument | #N/A (wrap in IFERROR) |
| Multiple-column return | Yes — one formula spills several columns | No — one value per formula |
Switch to XLOOKUP for any new workbook in Microsoft 365: it removes the first-column rule, the FALSE habit and the IFERROR wrapper in one move. Keep VLOOKUP in old shared workbooks and Google Sheets formulas that must run everywhere.
XLOOKUP in Google Sheets
Google Sheets supports XLOOKUP with the same six arguments and the same defaults as Excel, so the formulas above can be pasted over unchanged.
- Exact match by default and a working if_not_found argument behave identically in both apps.
- Wildcards work with match_mode 2 in Excel; Sheets accepts them the same way.
- In regional settings that use a comma as the decimal separator (much of Europe and Latin America), type semicolons between arguments: =XLOOKUP(A2; D2:D500; E2:E500; "Not found"). Excel behaves the same way.
- Older Sheets documents that predate XLOOKUP still run VLOOKUP fine — both functions coexist, so mixed formulas are safe.
Limitations and pro tips
- Version gate: XLOOKUP only exists in Microsoft 365 and Excel 2021+. Workbooks using it show #NAME? the moment an Excel 2019 user opens them.
- Both arrays must be the same height — build the habit of selecting D2:D500 and E2:E500 in one go.
- Double-click the fill handle (the small square at the corner of the selection) to copy the formula down to the last row in one go.
- Use search_mode -1 to grab the latest match from a log with repeated keys.
- For a two-way lookup (row and column), nest XLOOKUP inside XLOOKUP — it replaces the old INDEX/MATCH/MATCH pattern.
Need to pull many columns from another file rather than a single value? That is a table join, not a lookup: the free tools below do it without formulas.
Related Tools
Related Functions
Frequently Asked Questions
What does XLOOKUP do in Excel?
XLOOKUP searches one column for a value and returns items from another column on the same row — in either direction, with exact match by default and an optional "not found" message built in. It is the modern replacement for VLOOKUP and INDEX/MATCH.
Why is XLOOKUP not working in my Excel?
Mostly a version issue: XLOOKUP exists only in Microsoft 365 and Excel 2021 or later. Excel 2019 and older return #NAME?. The only fixes are upgrading, or switching that formula to INDEX/MATCH, which works everywhere.
How do I show "Not found" instead of #N/A with XLOOKUP?
Fill the fourth argument: =XLOOKUP(A2, D2:D500, E2:E500, "Not found"). Unlike VLOOKUP, you do not need to wrap anything in IFERROR — the message is part of the function itself.
Can XLOOKUP look to the left?
Yes. The lookup column and return column are independent ranges, so the return column can sit anywhere — left, right, or on another sheet. This is the main reason to prefer XLOOKUP over VLOOKUP.
Can XLOOKUP return multiple columns at once?
Yes. Pass a multi-column range as return_array, e.g. =XLOOKUP(A2, D2:D500, E2:G500), and the formula spills every requested column. Make sure the neighbouring cells are empty, or you will see #SPILL!.
Is XLOOKUP better than VLOOKUP?
For Microsoft 365 users, yes: exact match by default, left lookups, a built-in not-found message, multiple-column returns, and no breakage when columns are inserted. VLOOKUP stays useful for old Excel versions and maximum compatibility.
Does XLOOKUP work in Google Sheets?
Yes — Sheets supports XLOOKUP with the same arguments and defaults as Excel, including the if_not_found message. Only the argument separator changes in comma-decimal regional settings: use semicolons there.
Ready to try it on your own file?
Open Join Two Excel Tables by a Key Column — It's Free