If your XLOOKUP formula is not working, first check that your Excel version supports XLOOKUP, that the lookup and return ranges are the same size, and that the lookup value matches the source data exactly. The error or unexpected result usually points to the cause: #NAME? often means the function is unavailable or mistyped, #N/A means no match was found, and #SPILL! means Excel cannot place the returned results.
Start with a basic XLOOKUP check
XLOOKUP searches one range for a value and returns the corresponding value from another range. It uses exact matching by default, so you do not need to sort your data for a normal lookup. Its arguments are the value to find, the range to search, the range to return a result from, and optional settings for a missing match, match type, and search direction.
With product codes in A2:A6, product names in B2:B6, and the code to find in E2, use this formula:
=XLOOKUP(E2,A2:A6,B2:B6,"Not found")
If E2 contains P-104 and A4 contains P-104 while B4 contains Desk lamp, the formula returns Desk lamp. If there is no exact match, it returns Not found instead of showing #N/A. This example is a useful first test: if it works in a clean area of the workbook, the problem is likely in the original ranges, data, or formula rather than XLOOKUP itself.
Check your Excel version and the formula name
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016. If you open a workbook containing XLOOKUP in an unsupported version, Excel may display #NAME? or show the function name with an underscore prefix. Updating Excel or opening the file in a supported version is the direct fix; changing the formula cannot make an older version recognize a function it does not include.
If you believe your version supports it, check the spelling and punctuation. The function name is XLOOKUP, with no space. Also check that the formula begins with an equals sign and that text values are enclosed in straight double quotation marks. A copied formula can contain typographic “curly” quotes, which Excel may not interpret as formula string markers.
Excel normally uses commas between formula arguments in the examples here. Some regional settings use semicolons instead. If Excel rejects a correctly written formula at the first comma, try the separator used by your installation. This is a regional-list-separator difference, not a different XLOOKUP function.
Fix #N/A and values that look identical
#N/A means XLOOKUP could not find the lookup value under the selected matching rules. The values may look the same on screen but differ underneath. Common reasons include a leading or trailing space, a number stored as text in one range and as a number in the other, an extra character, or a value that has been rounded for display.
- Click the lookup cell and a likely matching source cell to inspect the formula bar and confirm their actual contents.
- Check for spaces copied from websites or exported files. TRIM can remove ordinary leading and trailing spaces from text, though it does not remove every possible nonprinting character.
- Compare data types. A numeric code such as 00123 may be stored as text, while 123 is numeric; those are not necessarily equivalent identifiers.
- Check that the lookup value is in the selected lookup range, not just somewhere else on the sheet.
For example, with invoice IDs in A2:A100, amounts in C2:C100, and the ID to find in E2, this formula returns a message if no exact match exists:
=XLOOKUP(E2,A2:A100,C2:C100,"Invoice ID not found")
If E2 contains an ID entered as text and column A stores the same-looking ID as a number, the message may appear even though the values seem alike. Choose a consistent format for the source and lookup value. Avoid converting identifiers casually: leading zeros can be meaningful, and a numeric conversion can remove them.
Confirm the lookup and return ranges line up
The lookup array and return array must correspond row by row and have compatible dimensions. If XLOOKUP searches A2:A20 but returns from C2:C19, the ranges are misaligned. Depending on the mismatch, Excel may return an error or a result that does not correspond to the intended record. Select ranges that start and end on the same rows, and check that both are vertical or both horizontal when using ordinary one-dimensional lookups.
With employee IDs in A2:A50, names in B2:B50, and the ID in E2, this aligned formula returns the matching name:
=XLOOKUP(E2,A2:A50,B2:B50,"ID not found")
When a formula is copied down a column, references can shift. If every row should search the same source table, lock those ranges with dollar signs. With lookup values in E2:E10, IDs in A2:A50, and names in B2:B50, enter this in F2 and fill it down:
=XLOOKUP(E2,$A$2:$A$50,$B$2:$B$50,"ID not found")
The dollar signs keep the source ranges fixed while E2 changes to E3, E4, and so on. If a column was inserted, deleted, or moved, inspect the formula references rather than assuming they still point to the intended fields.
Understand #SPILL! and multiple returned values
XLOOKUP can return an array, not just one cell. If the return array includes several columns, Excel may spill the matching row’s values into neighboring cells. A #SPILL! error usually means the destination cells are not available—for example, they contain data, merged cells block the result, or the formula is inside an Excel table that cannot accommodate that spill behavior.
With IDs in A2:A10, names in B2:B10, departments in C2:C10, and the requested ID in E2, this formula returns the matching name and department:
=XLOOKUP(E2,A2:A10,B2:C10,"ID not found")
Clear the cells where the result should appear, or move the formula to an area with enough empty space. If you only need one field, use a single return column instead. A green triangle or a #SPILL! warning can offer a way to inspect the obstruction; resolve the cause rather than deleting neighboring data without checking what it contains.
Check match settings, duplicates, and search direction
By default, XLOOKUP looks for an exact match and returns the first match it encounters from top to bottom. If a key appears more than once, the result may be valid but not the record you expected. Remove or resolve duplicate keys, or search from the bottom when the last matching row is the intended one.
With dates in A2:A100, corresponding status values in B2:B100, and a date to find in E2, use the following formula to return the last exact match:
=XLOOKUP(E2,A2:A100,B2:B100,"Date not found",0,-1)
The 0 specifies exact matching, and -1 searches from the last item toward the first. For approximate matching, XLOOKUP also supports the next smaller or next larger item, but the choice should reflect what the data means. For example, a tax or commission band lookup may need a carefully arranged threshold table; using approximate matching on an unsorted list can produce an unintended result. Binary search modes require data sorted in the appropriate order, so do not use them as a shortcut on unsorted data.
Wildcard matching is another special case. It is useful when the lookup value contains patterns such as an asterisk, but it must be requested explicitly. With customer names in A2:A30, account numbers in B2:B30, and a search pattern in E2, this formula enables wildcard matching:
=XLOOKUP(E2,A2:A30,B2:B30,"No match",2)
In wildcard mode, * represents any number of characters and ? represents one character. If you need to search for an actual asterisk or question mark, prefix it with a tilde (~). Without wildcard mode, these characters are treated as literal characters in an exact lookup.
Investigate formula text and calculation settings
If Excel displays the formula itself in the cell instead of a result, the cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell format to General, remove an unintended apostrophe, and re-enter the formula. On Windows, Ctrl+` toggles Show Formulas; the key is usually the grave-accent key near the top-left of the keyboard. Keyboard layouts can differ.
Also check calculation mode. On Windows Excel for Microsoft 365, open Formulas > Calculation Options and choose Automatic if the workbook is set to Manual. Press F9 to recalculate formulas. On a Mac, the F9 key may require the Fn key depending on keyboard settings; you can also use Excel’s calculation controls. If a formula updates only after recalculation, the issue may be calculation mode rather than lookup logic.
When Excel accepts the formula but returns an unexpected value, click the cell and inspect the formula bar. Confirm that the formula is referring to the intended workbook, sheet, and columns. A reference to a different sheet may be valid but still search the wrong data.
Choose error handling without hiding problems
The optional if_not_found argument lets you show a useful message when there is no match. This is often clearer than #N/A in a report. It only handles the not-found condition; it does not fix a malformed formula, an unsupported function, or an obstructed spill range.
With SKU values in A2:A500, stock counts in D2:D500, and a requested SKU in F2, this formula displays a clear message when the SKU is absent:
=XLOOKUP(F2,A2:A500,D2:D500,"SKU missing")
Be cautious about returning an empty string. A blank-looking result can mean either “not found” or that the matched return cell is genuinely blank. In operational workbooks, an explicit message is often easier to audit. Wrapping the entire lookup in IFERROR may conceal errors that should be corrected, so use targeted not-found handling unless you have a reason to handle other errors too.
Frequently Asked Questions
Why does XLOOKUP return #NAME?
Excel may not support the function in the version you are using, or the function name may be misspelled. XLOOKUP requires Microsoft 365 or Excel 2021 and later. It is not included in Excel 2019 or Excel 2016.
Why does XLOOKUP show #N/A when the value is there?
The displayed values may differ because of spaces, hidden characters, or a number-versus-text mismatch. Confirm that the lookup value is actually within the lookup range and that the formula is using the expected match mode.
Does XLOOKUP require sorted data?
No, not for its default exact-match search. Sorting is relevant when you choose certain approximate or binary search options. Binary search requires the data to be sorted in the correct order.
Can XLOOKUP return more than one column?
Yes. Select multiple columns as the return array, and Excel can spill the matching values into adjacent cells. The destination cells must be clear, or Excel may report #SPILL!.
What can I use instead of XLOOKUP in older Excel?
INDEX and MATCH can perform a similar lookup in older versions, though the formula is longer and has different behavior. VLOOKUP is another option when the lookup column is the leftmost column in the selected table and its return-column limitations fit your data.
Related
- Update Number Format in Excel: How to Change Formats
- US Number Format in Excel: Dates and Numbers
- Unmerge Shortcut in Excel: Windows Keys and Common Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Formula Not Calculating? Fix It Step by StepIf an Excel formula is not calculating, first check that calculation is set to Automatic: go to Formulas…
- Mode Not Working in Excel? Causes, Checks and FixesIf MODE is not working in Excel, the most common reason is that the selected range contains no…
- Excel Cell Shows Formula Not Result? Fix It FastIf your Excel cell shows the formula text instead of the calculated result, the most likely cause is…
- Why Is My VLOOKUP Not Working? Excel FixesVLOOKUP usually fails because the lookup value does not exactly match the first column of its table, the…
- Excel Find Not Working? Fix Search and Find SettingsIf Excel Find is not working, press Ctrl+F, open Options, and check the search scope and Look in…
- JS split is not a function? Fix It Fast“JS split is not a function” means you called .split() on a value that is not a string.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.