Why XLOOKUP Is Not Working in Excel: Causes and Fixes

Errors & fixes · Updated November 20, 2026

XLOOKUP usually fails because Excel does not support the function, the lookup and return ranges do not line up, or the lookup value differs from the stored data. Check the error message, confirm your Excel version, and compare the lookup value and ranges before changing the formula; these checks resolve most problems quickly.

What XLOOKUP does—and which Excel versions support it

XLOOKUP searches one range for a value and returns a corresponding value from another range. For example, it can find a product ID in one column and return that product’s price from another. Unlike older lookup methods, the return column can be on either side of the lookup column, and exact matching is the default.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. It is not available in Excel 2019 or Excel 2016. If an unsupported version opens a workbook containing XLOOKUP, it may show #NAME? rather than calculate the result. Updating Excel may help if your license includes a newer version; otherwise, use a compatible alternative such as INDEX and MATCH.

Excel’s argument separator can also depend on regional settings. The examples below use commas, as in a typical Microsoft 365 installation on Windows. If Excel reports a problem with the formula’s punctuation, your installation may require semicolons instead. That difference affects how arguments are separated; it does not change what XLOOKUP does.

Check the formula structure and range alignment

A basic XLOOKUP needs three things: the value to find, the range to search, and the range containing the result. You can also provide a value to display when there is no match. With product IDs in A2:A100, prices in C2:C100, and the requested ID in F2, use this formula. Assume F2 contains P-104 and the matching price in C17 is 24.50; the result is 24.50.

With product IDs in A2:A100, prices in C2:C100, and the lookup ID in F2: =XLOOKUP(F2,A2:A100,C2:C100,"Not found")

Make sure the lookup range and return range have the same shape and correspond row by row. If the lookup range is A2:A100 but the return range is C2:C99, the ranges are not aligned. Even equal-sized ranges can be wrong if one starts on a different row: searching A2:A100 while returning C3:C101 shifts every result by one record. Check both the start and end cells, not just the column letters.

Also confirm that the lookup range is a single row or column, while the return range has a compatible orientation. XLOOKUP can return more than one column when the lookup range is vertical and the return range includes several adjacent columns. That can be useful, but it means the results may need to spill into neighboring cells.

Understand the error message

The error often points to the cause. Start with the displayed result, then inspect the formula and data rather than replacing the function immediately.

  • #NAME? can mean your Excel version does not support XLOOKUP, the function name is misspelled, or a name used in the formula is undefined.
  • #N/A means Excel did not find a match, unless the formula provides a custom not-found result. The lookup value may genuinely be absent, or it may only appear absent because of spaces, data types, or a different spelling.
  • #VALUE! can indicate incompatible lookup and return array dimensions. Recheck that both ranges have matching shapes and intended row alignment.
  • #REF! points to an invalid reference, which can happen if a referenced range or sheet was deleted or changed.
  • #SPILL! can occur when XLOOKUP returns multiple cells but something is blocking the output area. Clear the obstructing cells or move the formula to an open area.

A custom not-found value can make a missing match easier to recognize, but it does not repair an incorrect range or a value that is stored differently. For example, if F2 contains P-999 and that ID is absent from A2:A100, the earlier example returns “Not found.” If P-104 is present but Excel still reports no match, investigate the data itself.

Look for text, number, and spacing differences

Two values that look alike on screen may not be equal to Excel. A product code stored as text can differ from a number stored as a numeric value. This is common with imported files, codes with leading zeros, and cells whose contents were pasted from another system. A code such as 00125 is especially worth checking: converting it to a number can remove the leading zeros and change the identifier.

Check a suspicious lookup cell and a matching-looking source cell by selecting each and inspecting the formula bar. Compare their number formats, and consider whether one value was imported as text. Avoid converting identifiers indiscriminately: for a code, preserving the exact characters may be more important than making the cells numeric. For genuinely numeric measurements, standardize both sides to numbers.

Leading or trailing spaces are another frequent cause. A cell may contain an invisible space after a name, or text copied from a website may include a nonbreaking space that ordinary trimming does not remove. If you clean the source data, do so consistently on both the lookup value and the lookup column, and keep a backup of the original data if it matters. Cleaning only one side can leave the mismatch in place.

Capitalization usually is not the issue: XLOOKUP’s standard text matching is not case-sensitive. If two entries differ only in uppercase and lowercase letters, XLOOKUP normally treats them as a match. Punctuation, spelling, extra spaces, and different characters are more likely explanations.

Check exact matching, wildcards, and search direction

With no match-mode argument, XLOOKUP looks for an exact match. That is usually right for names, product codes, and IDs. If you intentionally need an approximate result, use the appropriate match mode: one option finds an exact match or the next larger item, and another finds an exact match or the next smaller item. Approximate matching is useful for thresholds or bands, but the choice must fit how the data is organized.

Wildcards are a separate option for partial text searches. For instance, a search for a fragment such as “North” can match a longer entry when wildcard matching is enabled. With names in A2:A20, departments in B2:B20, and the text fragment in F2, this formula returns the department for the first matching name. Assume A7 contains Northwind and B7 contains Sales; if that is the first matching row, the result is Sales.

With names in A2:A20, departments in B2:B20, and a fragment in F2: =XLOOKUP("*"&F2&"*",A2:A20,B2:B20,"No match",2)

The search direction matters when there are duplicates. By default, XLOOKUP searches from the first item to the last and returns the first match. To return the most recent matching entry, search from the bottom upward. With transaction IDs in A2:A50, dates in B2:B50, and the ID to find in F2, this returns the date on the last matching row. If the last matching date is 10/31/2026, that is the result.

With transaction IDs in A2:A50, dates in B2:B50, and the ID in F2: =XLOOKUP(F2,A2:A50,B2:B50,"Not found",0,-1)

XLOOKUP also has binary search options for sorted data. Do not choose one just to make a formula seem faster: binary searches require the lookup data to be sorted in the required order, and an unsorted range can produce incorrect results without a useful warning. For ordinary lists, the default search is the safer choice.

Investigate references, tables, and spilled results

References can stop pointing at the intended data after rows or columns are inserted, deleted, or moved. Click the formula cell and review the colored range outlines Excel displays while editing. Confirm that the highlighted lookup range contains the keys you expect and that the return range contains the corresponding results. If the data is on another worksheet, check the sheet name in the reference and verify that the sheet still exists.

Structured table references can make formulas easier to maintain because they use column names rather than fixed cell addresses. However, a renamed table or column can invalidate a reference or leave the formula pointed at the wrong field. If a formula uses a table, check the table’s name and its header labels, and make sure there are no duplicate or unexpectedly blank headers.

If the return range includes several columns, XLOOKUP may return an array of results that fills neighboring cells. A value, another formula, a merged cell, or the edge of the worksheet can block that output and cause a spill error. Clear the intended output area or place the formula where enough empty cells are available. If you want only one result, select a single return column instead of a multi-column range.

A practical troubleshooting sequence

Work through the checks in order. This helps distinguish a version problem from a data problem and avoids adding complicated workarounds before you know what is wrong.

  1. Read the error. If it is #NAME?, verify that the Excel version supports XLOOKUP and check the spelling.
  2. Test the formula with a simple, known value that you can see in the lookup column. Confirm that the expected result is on the corresponding row.
  3. Inspect the references. Make sure the lookup and return ranges are the same length, start on the matching row, and point to the intended sheet and columns.
  4. Compare the actual cell contents on both sides. Look for text-versus-number differences, extra spaces, punctuation, and leading zeros.
  5. Confirm that the match and search settings fit your task. Use exact matching for identifiers, wildcard matching only for deliberate partial searches, and reverse search only when you need the last duplicate.
  6. If the formula returns multiple columns, check for obstructions in the cells where those results need to appear.

To inspect formulas across the sheet, Excel for Windows has the Show Formulas shortcut, Ctrl+grave accent. On Mac, the corresponding shortcut is Control+grave accent. The grave accent key is typically near the upper-left corner of the keyboard, but layouts vary. You can also select a formula cell and use the formula bar without changing the workbook display.

When to use an alternative

If your recipients use Excel 2019 or an earlier release that lacks XLOOKUP, choose a formula supported by their version. INDEX and MATCH can perform a similar lookup, though the formula is less direct. Before sharing a workbook, consider the Excel versions used by everyone who needs to open and calculate it; seeing a function in your own Microsoft 365 installation does not mean every recipient’s Excel can calculate it.

For a workbook that will remain in supported Microsoft 365 or Excel 2021 installations, replacing XLOOKUP solely because of a routine #N/A is usually not necessary. First check whether the value exists and whether the ranges and data agree. Switching functions will not resolve mismatched text, a shifted return range, or an incorrect search mode.

Frequently Asked Questions

Why does XLOOKUP show #NAME?
Most often, the Excel version does not support XLOOKUP, or the function name or a referenced name is misspelled. XLOOKUP is supported in Microsoft 365 and Excel 2021 or later. Check the version and the formula spelling first.

Why does XLOOKUP return #N/A when the value is there?
The displayed values may differ underneath: one may be text and the other numeric, or one may contain an extra space, different punctuation, or a hidden character. Check the actual cell contents and make sure the lookup range includes the apparent match.

Does XLOOKUP need the lookup column to be sorted?
No, not for its default search. The default searches from first to last and uses exact matching. Sorting matters if you specifically select one of XLOOKUP’s binary search modes; the data must then be sorted in the required order.

How do I return the last matching result?
Use XLOOKUP’s reverse search setting, which searches from the bottom upward. This is helpful when a key appears in multiple rows and the last row represents the newest or most relevant record. Check that the range includes all the rows you want searched.

Why does XLOOKUP work on my computer but not someone else’s?
The other person may be using Excel 2019 or an earlier version that does not support XLOOKUP. Ask which Excel version they use, or replace the function with a compatible lookup method if the workbook must work in that release.

Related

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.