XLOOKUP Excel Not Working? Fix Common Errors and Causes

If XLOOKUP is not working, first check that your Excel version supports it, then confirm the lookup and return ranges line up and the lookup value matches the source data exactly. For example, with product IDs in A2:A10, prices in B2:B10, and the ID to find in D2, use =XLOOKUP(D2,A2:A10,B2:B10,"Not found"); if D2 contains an ID in A2:A10, the formula returns the price from the corresponding row, otherwise it returns “Not found.”

What XLOOKUP Does and Which Excel Versions Support It

XLOOKUP searches one range or array for a value and returns a corresponding value from another range or array. It is designed to replace many common uses of VLOOKUP and HLOOKUP: it can search in either direction, uses exact matching by default, and does not need a column number that can break when columns are inserted. It can also return a result from multiple adjacent columns.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available as a built-in function in Excel 2019 or Excel 2016. A workbook containing XLOOKUP can still be opened in an older version, but that version may display #NAME? instead of calculating the formula. Excel for the web supports XLOOKUP as well. If you need to share the workbook with people using older desktop Excel, consider an alternative formula or provide calculated values.

Check the Formula Structure First

The function’s argument order is lookup value, lookup array, return array, optional value for no match, optional match mode, and optional search mode. The first three arguments are required. Since exact matching is the default, a straightforward formula can stay short; an optional not-found result often makes the output easier to understand than an error.

With employee IDs in A2:A100, names in B2:B100, and the ID to look up in E2, this formula returns the matching name or the specified message if there is no match: =XLOOKUP(E2,A2:A100,B2:B100,"ID not found"). The lookup and return ranges must correspond row by row. Here, a match in row 17 of the lookup range returns the value in row 17 of the return range.

For repeated lookups, absolute references can prevent the source ranges from shifting as you fill a formula down. With IDs in A2:A100, names in B2:B100, and lookup IDs beginning in E2, use =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"ID not found"). The result is the name for E2’s ID, while the source ranges remain fixed when the formula is copied to E3 or lower. The lookup value reference stays relative so it changes to E3, E4, and so on.

Why XLOOKUP Returns #N/A

#N/A usually means XLOOKUP did not find a match under the chosen match rules. Because exact matching is the default, a value that looks identical on screen may still differ in the underlying data. Common causes include a number stored as text, leading or trailing spaces, a different dash or apostrophe character, and a genuinely missing lookup value.

Check the data type. A numeric ID such as 1042 may be stored as the number 1042 in one range and as text in another. Formatting a cell to look like a number does not necessarily convert its stored value. Convert the source data consistently, or use a carefully chosen conversion in a helper column.
Check for extra spaces. A name such as “North” and one with an invisible trailing space are different text values. A helper column using TRIM can remove ordinary leading and trailing spaces, although it does not remove every kind of nonprinting character.
Check the lookup value itself. Confirm that the cell reference points to the intended cell and that the source list actually contains the value. A formula may be correct while the requested item is absent.
Use the not-found argument deliberately. It replaces the normal no-match result with a message, but it does not repair a mismatch. A message such as “Check ID” can make a missing value visible without disguising it as a valid result.

To test whether a key is stored as text, inspect the source cells and compare a suspect value with a known matching value. Avoid changing a large dataset blindly: converting identifiers can remove meaningful leading zeros or alter values that are intentionally text.

Resolve #VALUE!, #REF!, and #NAME? Errors

#VALUE! often points to incompatible range dimensions. The lookup array and return array should have matching shapes: if one is a vertical range with 20 rows, the other should also have 20 rows. A horizontal lookup range can return from a horizontal range with the same number of columns. Selecting a whole column for one argument and a shorter, unrelated range for another can produce an error or unintended results.

#REF! indicates a broken reference, which can happen if cells or a worksheet used by the formula were deleted or if a referenced range was otherwise damaged. Edit the formula and select the intended lookup and return ranges again. If a worksheet was renamed, Excel generally adjusts a valid reference automatically; deletion is more likely to leave a broken reference.

#NAME? has several possible causes. The most important XLOOKUP-specific cause is an Excel release that does not include the function. It can also appear if the function name is misspelled or a text argument is missing its quotation marks. With IDs in A2:A5, names in B2:B5, and an ID in D2, the correctly quoted not-found argument is =XLOOKUP(D2,A2:A5,B2:B5,"No match"). If D2 matches one of the IDs, Excel returns its corresponding name; otherwise it returns “No match.”

Some regional Excel settings use semicolons instead of commas between arguments. If a formula copied from another computer is rejected at entry, check the list separator used by your installation rather than changing the function’s logic. Excel normally applies the local separator automatically when you build a formula through the function interface.

Fix Incorrect Results and Match Settings

A formula can calculate successfully and still return the wrong item. XLOOKUP returns the first matching result it encounters by default. If the lookup list contains duplicate keys, the first occurrence is not necessarily the record you intended. To return the last matching result, set the search mode to reverse search.

With transaction IDs in A2:A20, transaction dates in B2:B20, and an ID in D2, use =XLOOKUP(D2,A2:A20,B2:B20,"Not found",0,-1). The formula searches from the bottom upward and returns the date associated with the last occurrence of D2; if there is no match, it returns “Not found.” The zero explicitly requests an exact match, while negative one as the final argument means search last-to-first.

Do not confuse match mode with search mode. Match mode controls what counts as a match: zero is exact, negative one allows the next smaller item, positive one allows the next larger item, and two enables wildcard matching. Search mode controls direction or search method. The binary search modes require the lookup array to be sorted in the appropriate order; using them on unsorted data can return incorrect results rather than a helpful warning. Unless you have a specific reason and verified sorting, leave search mode at its default linear search.

Wildcard matching is useful for text patterns, but it is not the default. With names in A2:A20, departments in B2:B20, and a partial name in D2, use =XLOOKUP(D2&"*",A2:A20,B2:B20,"No match",2). If D2 contains “Mar,” the formula finds the first name beginning with “Mar” and returns its department; it does not guarantee that the result is the only name beginning that way. The asterisk represents any number of characters, and the question mark represents one character. A tilde can escape a wildcard when you need to search for the character itself.

Handle #SPILL! and Multiple-Column Results

XLOOKUP can return more than one value when its return array contains multiple columns or rows. In current Excel versions with dynamic arrays, those results spill into adjacent cells automatically. A #SPILL! error usually means Excel cannot place the full result in the required cells, often because one or more destination cells already contain data, merged cells obstruct the output, or the formula is inside an Excel table that cannot accommodate a spilled array in that way.

With product codes in A2:A10, descriptions in B2:B10, prices in C2:C10, and a code in E2, use =XLOOKUP(E2,A2:A10,B2:C10,"Not found"). A matching code returns both the description and price in two neighboring cells, provided those output cells are clear; if there is no match, the formula returns “Not found” in the first result cell. Clear the obstructing cells or move the formula to an open area, then check whether the spill range is large enough.

If you expected several rows but got just one, check the shape and contents of the return array. XLOOKUP returns the corresponding row or column from the return array for the matched position; it is not a general-purpose filter for every record sharing a key. For all matching records, a function such as FILTER may be more suitable, where supported.

Make Lookups Easier to Maintain

Long fixed ranges are easy to mis-size when new records are added. Turning a dataset into an Excel table can make formulas more maintainable because table references expand as rows are added. On Windows, select the data and choose Insert > Table, or use Ctrl+T; on Mac, use Command+T after selecting the data. Check that the table has headers if the first row contains column names.

With an Excel table named Products containing columns ProductID and Price, and a lookup ID in E2, use =XLOOKUP(E2,Products[ProductID],Products[Price],"Not found"). If E2 matches a ProductID, the formula returns that row’s Price; otherwise it displays “Not found.” Table names and column names must match the workbook exactly. You can check or change a table’s name on the Table Design tab after selecting a cell in the table.

Tables are especially useful when the list grows, but they do not solve inconsistent keys, duplicate IDs, or incorrect column selection. For important reports, verify a few known matches manually and decide how duplicates should be handled. Also consider whether the result should update when source data changes or whether a fixed snapshot is required.

A Practical Troubleshooting Sequence

Work through these checks in order, starting with issues that can invalidate the function before investigating subtler data problems:

Confirm the workbook is open in Microsoft 365, Excel 2021, Excel 2024, or Excel for the web.
Read the error shown in the cell and check that the formula begins with the correct function name and has balanced parentheses and quotation marks.
Verify that the lookup array contains the value you want and that the return array has the same number of rows or columns.
Compare the stored types and characters in the lookup value and source keys; check for text-versus-number differences and extra spaces.
If the result is wrong rather than an error, inspect duplicate keys and match or search mode settings.
If the formula returns multiple cells, make sure the spill area is unobstructed.
Check calculation settings if the displayed result appears stale. On Windows and Mac desktop Excel, use Formulas > Calculation Options > Automatic. Excel for the web generally recalculates in the browser, but a workbook’s calculation behavior can still depend on its settings.

When troubleshooting a large workbook, test a simplified formula in an empty cell using a small, known set of values. This helps separate a syntax or version problem from issues in the original data. Once the cause is clear, restore the intended references and confirm the result against a known record before relying on it downstream.

Frequently Asked Questions

Why does Excel say XLOOKUP is not a valid function?
Most often, the workbook is open in Excel 2019 or an earlier release that does not include XLOOKUP. Use Microsoft 365, Excel 2021 or later, or Excel for the web, or replace the formula with a compatible alternative for the people who need to open the file.

Why does XLOOKUP return #N/A when the value is visibly there?
The stored values may differ even if they look alike. Check for number-versus-text differences, extra spaces, hidden characters, or different punctuation. Also confirm that the formula points to the correct lookup range.

Does XLOOKUP need the lookup column to be on the left?
No. Unlike the usual VLOOKUP arrangement, XLOOKUP takes separate lookup and return arrays, so the return range can be to the left or right of the lookup range. The arrays still need corresponding dimensions.

Can XLOOKUP return the last match?
Yes. Set the search mode argument to negative one to search from last to first. This is useful when duplicate keys represent successive records, but confirm that returning the last occurrence is the rule your task requires.

Why is XLOOKUP returning the wrong duplicate?
By default, it returns the first matching item from the start of the lookup array. If duplicates exist, decide whether the first or last record is correct, or make the lookup key unique before using the result.

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.