XLOOKUP Name Error: Causes, Checks, and Fixes in Excel

If XLOOKUP returns #NAME?, Excel usually does not recognize something in the formula: the function name may be misspelled, your Excel version may not support XLOOKUP, or a text value or name may be written incorrectly. Check the function spelling and Excel version first; a lookup value that simply is not present normally produces #N/A, not #NAME?.

What the XLOOKUP name error means

#NAME? is Excel’s way of saying it cannot identify part of a formula as a valid function, defined name, or text value. It does not mean that XLOOKUP searched correctly and failed to find a match. That difference matters: changing the lookup range will not repair a spelling or compatibility problem.

XLOOKUP searches one range or array for a value and returns a corresponding value from another range or array. For example, it can find a product code in one column and return the product name from another. It can search in either direction, and it uses an exact match by default. The function is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Excel 2019 and Excel 2016 do not include it.

With the product code to find in E2, codes in A2:A10, and product names in B2:B10, this is a valid basic formula:

=XLOOKUP(E2,A2:A10,B2:B10)

If E2 contains P-104, A7 contains P-104, and B7 contains Keyboard, the formula returns Keyboard. If the formula is accepted but no code matches, the result is ordinarily #N/A. If Excel shows #NAME?, start by checking whether it understands the function and every name or text item in the formula.

Check whether your Excel version supports XLOOKUP

Version compatibility is one of the most common reasons for this error. A workbook containing XLOOKUP can be opened in an older desktop edition, but that edition may not calculate the function. Depending on the version and how the workbook was saved, an older Excel installation may show #NAME? or an _xlfn. prefix before the function name. The formula may still work when opened in a supported version; the prefix does not mean the lookup ranges are necessarily wrong.

To check your edition in Excel for Windows, select File > Account, then look under Product Information. If you have a perpetual edition, the product name and version are shown there. If you use Excel for the web, XLOOKUP is available when you are signed in to a supported Microsoft 365 environment. Excel for Mac also supports XLOOKUP in Microsoft 365 and Excel 2021 or later, though its account and update screens differ from Windows.

When a workbook will be used by people with mixed Excel versions, confirm their versions before building the file around XLOOKUP. A formula that calculates on your computer might not calculate on a colleague’s older installation. Saving the workbook as an .xlsx file does not add the function to software that lacks it.

Verify the function name and formula syntax

Inspect the formula in the formula bar, not just the displayed cell result. The function name must be spelled XLOOKUP. A missing letter, an extra character, or a typo can make Excel treat the name as an unknown function. Re-entering the function through Excel’s formula autocomplete can help prevent typing mistakes. If autocomplete does not offer XLOOKUP, check the installed Excel version before spending time adjusting the ranges.

A basic XLOOKUP formula has this structure: lookup value, lookup array, return array, and optional arguments. The lookup array is where Excel searches; the return array is where it retrieves the result. Those two arrays should correspond in size and direction. A vertical lookup range normally pairs with a vertical return range, and a horizontal lookup range with a horizontal return range.

For example, if E2 holds the code, A2:A10 holds codes, and B2:B10 holds names, this version displays a message rather than #N/A when the code is absent:

=XLOOKUP(E2,A2:A10,B2:B10,"Not found")

With the same layout, it returns the matching name if found and the text Not found if there is no exact match. The fourth argument is optional and is called the if_not_found value. Text entered directly in a formula must be enclosed in straight double quotation marks. Without those marks, Excel may interpret a word such as Not as a defined name, and an unrecognized name can trigger #NAME?.

Use commas between arguments in the examples here, as in standard English-language Excel settings. Some regional settings use semicolons as formula separators. If Excel rejects a correctly structured formula at the commas, check the list separator configured for your system and use the separator Excel expects; that syntax problem may produce a different formula error, rather than indicating that XLOOKUP is unsupported.

Look for unquoted text and misspelled names

Not every #NAME? in a formula points to the function itself. Excel can also return this error when it encounters a word that it cannot interpret as text or as a defined name. In a formula, literal text needs quotation marks. Cell references and function names do not. If the text is stored in a cell, refer to that cell instead of typing the text into the formula.

Defined names are labels assigned to cells or ranges, such as a name for a product table. If an XLOOKUP formula uses a defined name, confirm that the name exists and is spelled exactly as written. In Excel for Windows, open Formulas > Name Manager to review workbook and sheet-level names. A name can also be valid only on a particular worksheet, which can affect formulas entered elsewhere.

Check worksheet references as well. A worksheet name containing spaces must be enclosed in single quotation marks when it is part of a reference, and an exclamation mark separates the sheet name from the cell or range reference. A misspelled sheet name can make a formula fail, though Excel may display a reference error instead of #NAME?, depending on what part of the formula it cannot resolve. Formula autocomplete and selecting the range with the mouse can reduce these reference typos.

When a formula is long, read it from left to right and verify each item: the function, each cell or range reference, any defined name, and every text literal. Do not assume that a name is valid just because it appears in a column heading; headings do not automatically become formula names.

Tell #NAME? apart from other XLOOKUP errors

Identifying the exact error helps narrow the cause. These results mean different things:

#NAME? means Excel cannot recognize part of the formula, such as an unsupported or misspelled function or an undefined name.
#N/A usually means the lookup value was not found. Check for extra spaces, inconsistent codes, or a value that genuinely is not in the lookup array.
#VALUE! can indicate a problem with an argument or incompatible array dimensions. Confirm that the lookup and return arrays have compatible shapes.
#REF! points to an invalid reference, for example if a referenced row, column, or sheet was deleted.

XLOOKUP’s default match mode is exact match, so a near match is not enough. A code that looks identical on screen may contain a trailing space, or one entry may be stored as text while another is numeric. Those inconsistencies usually cause an unsuccessful match rather than #NAME?, but they are worth investigating after Excel accepts the formula. The error shown in the cell is a useful clue, not just a generic warning.

A practical troubleshooting sequence

Work through these checks in order, testing the formula again after each relevant change:

Confirm the displayed error is exactly #NAME?. If it is #N/A, troubleshoot the value and lookup data instead.
Check the Excel edition and update status. XLOOKUP requires Microsoft 365 or Excel 2021 or later.
Check that the function is spelled XLOOKUP and that Excel recognizes it in formula autocomplete.
Review each argument, especially text entered directly in the formula, defined names, and references to other worksheets.
Verify that the lookup and return ranges are intentional and have matching dimensions.
If the workbook is shared, open it in a supported Excel version and ask whether the error occurs for other users too.

For a controlled test, try the function with a small, known set of cells rather than changing the original formula repeatedly. With E2 containing P-104, A7 containing P-104, and B7 containing Keyboard, the basic formula should return Keyboard. If even that simple formula produces #NAME?, compatibility or function recognition is more likely than a problem in the full workbook. If the simple test works, compare its working references and syntax with the original formula.

You can also make blank lookup cells stay visually blank. With the lookup value in E2, codes in A2:A10, and names in B2:B10, use:

=IF(E2="","",XLOOKUP(E2,A2:A10,B2:B10,"Not found"))

This returns a blank when E2 is blank, Keyboard for the matching P-104 example, or Not found for a nonblank value with no match. It does not fix an unsupported XLOOKUP function: an older Excel version still cannot calculate the inner function. Also, a cell containing a formula that returns an empty string is not truly empty, which can matter in later calculations.

When to use an alternative

If you must support Excel 2019 or Excel 2016, choose a lookup method available in those versions or update the software. VLOOKUP is a familiar option when the lookup column is to the left of the return column. INDEX with MATCH can handle more layouts, including returning values from a column to the left. These functions have different syntax and limitations, so replacing XLOOKUP is not always a matter of changing only its name.

For example, VLOOKUP requires a column index number and typically searches the first column of its table array. That makes it less flexible if columns are rearranged or if the return data sits to the left. INDEX and MATCH can separate the search range from the return range, but take more setup. Before replacing a formula in a shared workbook, check that the alternative supports the needed exact-match behavior and that the receiving Excel versions calculate it correctly.

Another option is to have workbook users open the file in Microsoft 365 or Excel 2021 and later. That avoids rebuilding formulas, but it may not be practical if recipients cannot change their software. Do not convert XLOOKUP to a different function solely because a match is missing: if the original error is #N/A, first check the source values and matching rules. If it is #NAME?, an alternative is useful mainly when the cause is a version that does not support XLOOKUP.

Frequently Asked Questions

Does #NAME? mean XLOOKUP could not find my value?
No. A value that is not found normally gives #N/A. #NAME? indicates that Excel cannot recognize something in the formula, such as the function name, a text literal, or a defined name.

Why does XLOOKUP work on one computer but not another?
The computers may have different Excel versions. Microsoft 365 and Excel 2021 or later support XLOOKUP; Excel 2019 and Excel 2016 do not. Check the product version on the computer showing the error.

Can I use XLOOKUP in Excel 2019?
No. Excel 2019 does not include XLOOKUP. Use a compatible lookup approach such as VLOOKUP or INDEX with MATCH, or open the workbook in a supported Excel version.

Why does my formula show _xlfn.XLOOKUP?
The prefix can appear when the Excel version opening the workbook does not recognize the function. Open the workbook in Microsoft 365 or Excel 2021 or later, or replace the formula with one compatible with the older version.

Will adding an if_not_found value fix #NAME?
Not if the cause is an unsupported or misspelled function, or an undefined name. The if_not_found argument supplies a result when a valid XLOOKUP cannot find a match; it does not make Excel recognize an unavailable function.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.