XLOOKUP Not Working? Common Causes, Checks, and Fixes

If XLOOKUP is not working, first check that your Excel version supports it, then verify the lookup value and lookup range match exactly and that the lookup and return ranges have compatible sizes. In Microsoft 365 for Windows, XLOOKUP normally searches for an exact match by default; the error or unexpected result often points to a version issue, a hidden difference in the data, or an incorrect range.

Check whether your Excel version supports XLOOKUP

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also available in Excel for the web. It is not available in Excel 2019 or Excel 2016. If you open a workbook containing XLOOKUP in an unsupported release, the formula may display #NAME? because that version does not recognize the function. Updating an older license may not be possible; check the product name under File > Account in Windows Excel, or use an alternative lookup formula if the workbook must work in an older version.

Do not confuse a missing function with a typing error. The function name is XLOOKUP, with no space. In a supported version, enter the formula in a normal worksheet cell and check whether Excel recognizes the name as you type. Excel for Mac and Excel for the web also support XLOOKUP in current compatible versions, though ribbon layouts differ from Windows. The formulas below use comma separators; some regional settings use semicolons between arguments, so Excel may require that local separator when you enter a formula.

Understand the arguments before changing the formula

The basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first argument is the value to find, the second is the one-dimensional range to search, and the third is the range containing the answer. The optional arguments let you set a message for missing matches, request a different match type, or change the search direction.

With product codes in A2:A100, product names in B2:B100, and the code to search for in E2, this formula returns the corresponding name or the words Not found:

Assuming codes are in A2:A100, names are in B2:B100, and the search code is in E2: =XLOOKUP(E2,A2:A100,B2:B100,"Not found",0)

The final 0 explicitly requests an exact match. It is optional because exact matching is already XLOOKUP’s default. When a match is found, the function returns the value from the corresponding position in the return range. For example, if E2 contains a code in A7, the result comes from B7. If the code does not appear, the formula returns the specified missing-value message instead of #N/A.

Diagnose #N/A and values that look identical

#N/A usually means Excel did not find a match under the chosen match rules. Two values can look identical on screen but differ underneath. Common examples include a trailing space, a nonbreaking space copied from a webpage, different punctuation, or a number stored as text in one range and as a number in the other. Check the original cells in the formula bar and compare their types and contents rather than relying only on their displayed formatting.

Check spaces: compare a suspected cell with a cleaned version. The TRIM function removes ordinary leading and trailing spaces and reduces repeated spaces between words, but it does not remove every special whitespace character.
Check number versus text: a cell formatted to look like a number may actually contain text. Formatting alone does not convert the stored value.
Check characters: hyphens, apostrophes, leading zeroes, and copied nonprinting characters can make codes differ. Leading zeroes may be meaningful identifiers, so do not convert them to numbers unless that is appropriate for the data.

For an initial test, if the lookup value is in E2 and the source key is in A2, this formula shows whether E2 equals A2 after removing ordinary spaces:

Assuming the two values being compared are in E2 and A2: =TRIM(E2)=TRIM(A2)

If it returns FALSE, inspect the data more closely. Applying TRIM only to the lookup value will not fix dirty values throughout the source range. A reliable approach is to add a helper column containing cleaned keys, fill it down, and search that helper column. If special characters are involved, identify the specific character and clean it deliberately; a broad conversion can alter legitimate identifiers.

Verify the lookup and return ranges

The lookup array and return array must correspond position by position. If the match is in the fifth cell of the lookup array, XLOOKUP returns the fifth cell of the return array. A range that starts on the wrong row can therefore produce a plausible but incorrect answer. Check that both ranges cover the intended records and begin and end on matching rows.

XLOOKUP can return values from either side of the lookup column, unlike the traditional VLOOKUP arrangement, but the arrays still need compatible dimensions. A vertical lookup range should normally pair with a vertical return range of the same height; a horizontal lookup range should pair with a horizontal return range of the same width. If you select an entire column for one array and only part of a column for the other, correct the ranges so the positions line up. Also check for accidental absolute references or shifted references after copying a formula. Dollar signs such as $A$2:$A$100 lock a range when a formula is filled, while relative references move with the formula.

Fix #VALUE!, #NAME?, and other formula errors

#VALUE! can indicate incompatible array shapes or another invalid argument. Make sure the lookup and return arrays are not accidentally mismatched—for example, searching a horizontal row while asking for a multirow return range. XLOOKUP can return multiple columns or rows when the return array includes them, but the requested output must fit the surrounding worksheet cells.

#NAME? commonly means the function is unavailable in that Excel version or the function name was misspelled. It can also occur when text in a formula is not enclosed in straight double quotation marks. For example, a literal message such as Not found needs quotes in the formula. If the formula is accepted but displays an error from a cell in the return range, the source cell itself may contain an error; XLOOKUP does not automatically remove errors in returned data.

To show a message only when the lookup produces #N/A, wrap the lookup in IFNA. Assuming the search value is in E2, keys are in A2:A100, and results are in B2:B100: =IFNA(XLOOKUP(E2,A2:A100,B2:B100),"No match"). This handles a missing match but does not disguise every problem: errors such as #REF! or #VALUE! can still indicate a broken formula or data issue and should be investigated.

Check match mode, wildcards, and search direction

With no match-mode argument, XLOOKUP looks for an exact match. If you set a different mode, confirm that it suits the data. Match mode -1 asks for an exact match or the next smaller item; 1 asks for an exact match or the next larger item; and 2 enables wildcard matching. A wildcard search is useful for text patterns, but it is not the same as an exact search. In wildcard mode, * represents any sequence of characters and ? represents one character; use a tilde to treat a wildcard character literally.

The search-mode argument controls direction or algorithm. Its default searches from the first item to the last. Using -1 searches from the last item to the first, which can return the most recent matching entry when records are arranged chronologically. Modes 2 and -2 use binary search and require the lookup data to be sorted in the appropriate order. If the range is not sorted as required, the result can be wrong. For most everyday lookups, leave search mode blank unless you have a specific reason to change it.

Check calculation, spilled results, and workbook links

If the formula is correct but the displayed result seems stale, check whether calculation is set to Automatic. In Windows Excel, use Formulas > Calculation Options > Automatic. Excel for Mac has the Calculation options on the Formulas tab as well, though the ribbon may be arranged differently. If calculation is manual, formulas may not update until recalculated. Press F9 on Windows to recalculate; on Mac, the equivalent commonly depends on the keyboard settings, and Fn+F9 may be needed.

XLOOKUP can return an array, such as several adjacent columns from the matching row. If cells where the result needs to spill are already occupied, Excel may show #SPILL!. Clear the blocking cells, check for merged cells in the spill area, and make sure the formula is not inside an Excel Table when the intended result is a multi-cell spill. A single-value lookup does not need extra spill space.

When a lookup refers to another workbook, verify that the source workbook path and sheet references remain valid and that the linked file is accessible. A broken reference can produce an error that looks like a matching problem. If the workbook was moved, renamed, or received from another person, inspect the formula’s references before rebuilding the lookup.

A quick troubleshooting sequence

Work through the checks in order rather than rewriting a long formula immediately. This makes it easier to identify whether the cause is compatibility, data, or formula structure.

Confirm that the Excel version supports XLOOKUP and that the function name is spelled correctly.
Temporarily use a short test range and an explicit exact match to isolate the formula.
Check whether the lookup value actually occurs in the lookup range, including spaces, data type, punctuation, and leading zeroes.
Confirm that lookup and return arrays are aligned and have compatible dimensions.
Review optional match and search modes, especially approximate matching and binary search.
Investigate the specific error code, calculation setting, blocked spill cells, or broken external reference.

For a transparent test, select a known key from the lookup range and use it as the search value. If that works but the real value does not, focus on the real value’s type or characters. If neither works, focus on the ranges, formula syntax, version, or workbook calculation state.

When to use an alternative

If a workbook must be used in Excel 2019 or 2016, use a compatible function such as INDEX with MATCH, or VLOOKUP when the lookup column is the leftmost column of the table and the return column is to its right. These alternatives have different constraints. VLOOKUP relies on a column index and cannot directly return a value to the left of the lookup column; INDEX and MATCH can be arranged for that purpose but require a more involved formula. If all users have a supported Excel version, replacing XLOOKUP solely because one formula fails may hide the actual data or range problem.

Frequently Asked Questions

Why does XLOOKUP show #N/A when the value is there?
It may only appear to be the same value. Check for spaces, nonbreaking spaces, different punctuation, text-versus-number differences, or a lookup range that omits the row. Compare the underlying cell contents and test with a known key.

Why does Excel say XLOOKUP is not a valid function?
The function may be misspelled, or the workbook may be open in Excel 2019 or 2016, which do not support XLOOKUP. Microsoft 365, Excel 2021 and later, and compatible current Excel for the web versions support it.

Does XLOOKUP require the lookup column to be first?
No. Unlike VLOOKUP’s usual table arrangement, XLOOKUP takes the lookup array and return array as separate arguments, so the return values can be on either side of the lookup values.

How do I return a blank instead of an error when nothing matches?
Use the optional if_not_found argument and put an empty text string in straight quotation marks. With the lookup value in E2, keys in A2:A100, and results in B2:B100: =XLOOKUP(E2,A2:A100,B2:B100,"",0). This returns a blank-looking result for a missing key, but it does not correct errors in the source data.

Can XLOOKUP return the last matching entry?
Yes. Set the search-mode argument to -1 to search from the last item toward the first. This is useful when the last matching row represents the newest record, provided the data is arranged so that interpretation is valid.

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.