An Excel XLOOKUP #NAME? error usually means Excel does not recognize the XLOOKUP function or another name used in the formula. Check your Excel version first, then check the function spelling, any unquoted text, and any named ranges the formula refers to.
What the XLOOKUP name error means
#NAME? is Excel’s way of saying that it cannot resolve a name in a formula. The name might be a function, a defined name, or a word Excel is treating as a name because it is not enclosed in quotation marks. If XLOOKUP itself is unrecognized, the formula cannot run, regardless of whether the lookup and return ranges are otherwise correct.
This is different from #N/A. That error usually means Excel ran the lookup but could not find the requested value under the formula’s matching rules. It is also different from #REF!, which points to an invalid cell reference, and #VALUE!, which can indicate incompatible argument sizes or types. Identifying the exact error helps avoid changing the lookup ranges when the real problem is that Excel does not know the function.
A #NAME? result does not, by itself, prove that the workbook is damaged. It often comes from a version mismatch, a spelling mistake, or a formula reference that needs to be defined or quoted.
Check the XLOOKUP syntax
XLOOKUP searches one range or array for a value and returns a corresponding item from another range or array. Its basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The square brackets in Microsoft’s syntax indicate optional arguments; do not type the brackets around an optional argument into the formula.
For example, with employee names in A2:A6, employee IDs in B2:B6, and the name Mina in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). If Mina is in A4 and the corresponding ID in B4 is 104, the result is 104. The quoted fourth argument supplies a message only if no match is found.
The lookup array and return array should normally cover corresponding rows or columns. In this example, both ranges contain five rows. XLOOKUP can search vertically or horizontally, and the return range can be to either side of the search range. It does not require the lookup data to be sorted for its default exact match.
Common causes of #NAME? in an XLOOKUP formula
- A misspelled function name: A missing or transposed letter means Excel may interpret the entry as an unknown name rather than a valid function. Re-enter the function carefully or select XLOOKUP from Excel’s formula suggestions.
- An unsupported Excel version: Older editions, including Excel 2019 and Excel 2016, do not include XLOOKUP. A workbook containing the function may therefore show an error when opened in one of those editions.
- Text that is not in quotation marks: Text entered as a formula argument must be enclosed in straight double quotation marks when it is meant to be literal text. Without them, Excel may try to interpret a word as a defined name. The example’s “Not found” argument is therefore quoted.
- An undefined or unavailable name: If a formula uses a named range such as EmployeeNames, Excel must have a defined name with that spelling and scope. A typo, deleted name, or sheet-level name that is not visible from the formula’s sheet can lead to #NAME?.
- A function name or reference pasted incorrectly: Replacing a straight quotation mark with a typographic quote, or pasting unexpected characters into a formula, can prevent Excel from reading it as intended. Re-entering the affected part directly in the formula bar can help isolate this.
Not every formula problem produces #NAME?. For instance, a misspelled cell address may produce a different error, and a valid XLOOKUP that finds no matching item usually returns #N/A unless an if-not-found value is supplied. Focus on the part Excel cannot identify rather than assuming every error is a failed search.
Confirm that your Excel version supports XLOOKUP
XLOOKUP is available in Microsoft 365 and Excel 2021 and later, including Excel 2024. It is not a standard function in Excel 2019 or Excel 2016. If someone created the workbook in a newer edition and another person opens it in an older one, the older installation may not be able to calculate the formula. In some cases, an older version may display an _xlfn. prefix before an unsupported function name.
On Excel for Microsoft 365 on Windows, open File > Account and look under Product Information to identify the installed product. About Excel provides version details. If the installation is Microsoft 365 but appears out of date, use File > Account > Update Options > Update Now, if that option is available. Updates managed by an organization may be controlled by its administrator.
On a Mac, use Excel > About Microsoft Excel to check the version. Updates are handled through Microsoft AutoUpdate or the update method provided by the organization. Excel for the web supports XLOOKUP in current Microsoft 365 web use, although its menus and update controls are not the same as the Windows desktop app. If the same workbook works in the web app but not on a desktop computer, compare the desktop version before rewriting the formula.
Updating is not always an option. A recipient may be using a fixed older edition, or the workbook may need to work with older software. In those cases, use a compatible alternative and test it in the oldest version that must open the file.
Diagnose the formula one check at a time
- Read the exact error. Confirm that the cell shows #NAME? rather than #N/A, #VALUE!, or #SPILL!. These errors point to different issues.
- Check the function name. Select the formula cell and inspect the formula bar. Confirm that the function is spelled XLOOKUP and that its opening parenthesis follows the function name.
- Confirm version support. Check the Excel edition on the computer returning the error. If XLOOKUP is unsupported, editing the ranges will not make that function available.
- Inspect text and defined names. Make sure literal text has straight double quotation marks. For named ranges, open Formulas > Name Manager and verify the spelling, the Refers to entry, and the name’s scope.
- Try a small controlled example. In a blank area of a supported version, enter a simple XLOOKUP using two short ranges with an obvious matching value. If that works, the original issue is more likely to be a name, quote, or reference in the full formula.
- Check the workbook where the error appears. A formula can calculate in one computer’s newer Excel and fail on another computer’s older edition. Ask which app and version is being used to open the file.
When entering formulas in Excel, the argument separator can depend on regional settings. The examples here use commas, as in the standard English-language syntax; some installations expect semicolons instead. A separator mismatch usually triggers a formula-entry or parsing problem, not proof that XLOOKUP is unavailable. Follow the separator Excel accepts in that installation.
Distinguish a name error from a failed lookup
Once Excel recognizes XLOOKUP, it can still fail to find a match. The default match mode is exact match, so differences such as an extra space, a different spelling, or a number stored as text can prevent a match. That generally results in #N/A, not #NAME?. Adding an if-not-found message can make a missing result easier for users to understand, but it does not repair an unrecognized function or undefined name.
For a no-match result, compare the lookup value with the source data. Check leading or trailing spaces, whether values are numbers or text, and whether the search range includes the expected record. Also verify that the return range corresponds to the same records. A lookup range and a return range with different dimensions can cause a separate formula error; it is not the usual explanation for #NAME?.
XLOOKUP can also return multiple values when its return array contains multiple columns or rows. If the result cannot spill into occupied cells, Excel may show #SPILL!, which is another distinct issue. Clearing the blocking cells may address that problem, but it will not help when the formula itself returns #NAME?.
Use an alternative when XLOOKUP is unavailable
If you must support Excel 2019 or an earlier edition, INDEX and MATCH can perform a similar exact lookup. With employee names in A2:A6, IDs in B2:B6, and Mina in D2, enter =INDEX(B2:B6,MATCH(D2,A2:A6,0)). If Mina is in A4, MATCH returns the position 3 within A2:A6, and INDEX returns the third item in B2:B6: 104.
The final 0 in MATCH requests an exact match. INDEX and MATCH work in many older Excel editions, which makes them useful when compatibility matters. However, this example returns #N/A when there is no exact match, unless you add error handling. It also uses two functions rather than XLOOKUP’s optional not-found argument, so the formulas are not identical in every behavior.
VLOOKUP is another older option when the lookup value is in the leftmost column of a table and the return column is to its right. That limitation matters: VLOOKUP cannot naturally return a value from a column to the left of its search column. Choose the alternative based on the oldest Excel version your users need and the layout of the data, not simply on which formula is shortest.
Frequently Asked Questions
Why does XLOOKUP show #NAME? in Excel 2019?
Excel 2019 does not include XLOOKUP as a standard worksheet function. Open the workbook in Microsoft 365 or Excel 2021 or later, or replace the formula with a compatible method such as INDEX and MATCH.
Does #NAME? mean XLOOKUP could not find my value?
Usually, no. A supported XLOOKUP that cannot find an exact match normally returns #N/A, unless you provide an if-not-found result. #NAME? indicates that Excel cannot recognize a function or another name in the formula.
Why does a word in my XLOOKUP formula cause #NAME?
Excel may be treating the word as a defined name. If you mean literal text, enclose it in straight double quotation marks. If it is meant to refer to a named range, check that the name exists and is in scope.
Can I open a workbook with XLOOKUP in an older Excel version?
You may be able to open the workbook, but an older edition that lacks XLOOKUP cannot calculate that formula normally. Use a compatible formula if the workbook must function there, and check the result in that older edition.
Will adding an if-not-found message fix #NAME?
No. That argument handles a valid lookup that finds no match; it cannot make an unsupported function or an undefined name valid. Resolve the name error first, then use the optional argument if you want a clearer message for missing values.
Related
- Excel Unmerge Cells Shortcut: Keys, Steps, and Fixes
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Page Break Not Going Away? Fix Persistent LinesIf an Excel page break will not go away, first switch to View > Normal; if the line…
- Excel Freeze Panes Not Working? Fix the SelectionIf Excel is not freezing the rows or columns you selected, click the cell immediately below the rows…
- Excel Flash Fill Down: How to Use It and Fix IssuesYes. In Excel for Microsoft 365 on Windows, enter one or two examples in an empty adjacent column,…
- Excel #REF! Error: How to Fix Broken Cell ReferencesTo fix an Excel #REF! error, edit the formula that displays it and replace the invalid reference with…
- Excel Multiply Formula Not Working? Fix Common CausesIf an Excel multiplication formula is not working, first check that it uses the asterisk (*) between valid…
- Excel Cell List Drop-Down: Create and Fix OneTo create an Excel cell list drop-down in Microsoft 365 for Windows, select the cell, choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.