XLOOKUP #N/A Error: Causes and Fixes

An #N/A error in XLOOKUP usually means Excel could not find the lookup value in the lookup range, or that the matched return cell itself contains #N/A. Check that the value and lookup range really match, then use XLOOKUP’s optional if_not_found argument if you want a useful message instead of the error.

What #N/A means in XLOOKUP

XLOOKUP searches one range for a value and returns a corresponding value from another range. By default, it looks for an exact match. If it cannot find one, Excel returns #N/A. For example, a product code with an extra space, a different data type, or a genuine absence from the list can all cause the same visible result.

There is an important second possibility: XLOOKUP may find the lookup value successfully, but the corresponding cell in the return range may already contain #N/A. In that case, the error is coming from the returned data, not from a failed search. This distinction matters because changing the lookup range will not fix an error stored in the matching result cell.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In Excel 2019 or 2016, the function is generally not available; entering it may produce #NAME? rather than #N/A. Excel for the web and current Mac versions also support XLOOKUP, although ribbon layouts can differ from Excel for Windows.

How XLOOKUP searches

The function’s arguments let you specify the value to find, the range to search, the range to return a result from, and optional behavior for missing values and search order. A common pattern is to search a product-code column and return the product name from the neighboring column.

With product codes in A2:A100, product names in B2:B100, and the code to find in E2, the formula is =XLOOKUP(E2,A2:A100,B2:B100). If E2 exactly matches a value in column A, Excel returns the value on the same row from column B. If there is no exact match, the default result is #N/A.

Unlike older lookup patterns that often require a lookup column at the left edge of a table, XLOOKUP can search and return from separate ranges in either direction. The lookup and return ranges should correspond row by row and have compatible dimensions. If you accidentally select a return range that starts or ends on a different row, the formula may return a result from the wrong record even when it does not show an error.

Diagnose the lookup before changing the formula

Start with the value being searched for, then compare it with the source list. A quick visual scan is not always enough: two codes can look identical while one contains a trailing space, a nonbreaking space, or a character copied from another system. Confirm that the expected value exists in the precise cells included in the lookup range, not just somewhere else on the worksheet.

Click the lookup cell and the likely matching source cell. Compare their contents in the formula bar, and check for leading or trailing spaces.
Confirm that the lookup range includes the row containing the value. A range ending at row 100 will not search a new record entered in row 101.
Check whether one value is stored as text and the other as a number. A code such as 0042 may also have lost its leading zero if Excel interpreted it as a number.
Inspect the matched return cell for an existing error. If it contains #N/A, XLOOKUP can return that error even though the lookup succeeded.
Check the formula’s row alignment and confirm that the lookup and return ranges point to the intended columns.

A useful test is to copy the exact source value and paste it into the lookup cell. If the formula then works, the original input likely differed in a way that was hard to see. This is a diagnostic, not always a final fix: replacing a value can hide a data-cleaning problem that will recur when the next file is imported.

Text, numbers, spaces, and hidden characters

Values that appear alike can be stored differently. For instance, the number 42 and the text string “42” are not necessarily interchangeable in a lookup. Product identifiers, postal codes, and account numbers are often best stored consistently as text, especially when leading zeros are meaningful. Do not convert an identifier to a number if doing so would change its actual code.

Whitespace is another frequent cause. Excel’s TRIM function removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every invisible character; CLEAN removes many nonprinting ASCII characters, but neither function removes all possible Unicode whitespace, including some nonbreaking spaces. If imported data is inconsistent, create cleaned helper columns for both the lookup values and the source list, then search those normalized columns.

For example, with raw source codes in A2:A100, you could place a cleaned version in C2 and fill it down. With the raw code in E2 and corresponding names in B2:B100, search C2:C100 after applying the same cleanup to the input. If the lookup value is cleaned inside the formula, use =XLOOKUP(TRIM(E2),C2:C100,B2:B100,"Not found"). This only helps when the source values in column C have also been cleaned consistently; cleaning just one side may leave the mismatch intact.

Choose exact, approximate, or wildcard matching

For identifiers, names, and most everyday lookups, the default exact match is generally the safest choice. XLOOKUP is also case-insensitive for ordinary text searches, so it does not distinguish “abc” from “ABC.” If capitalization is intended to separate records, XLOOKUP alone does not enforce that distinction.

The optional match_mode argument changes what counts as a match. A value of 0 means exact match and is the default. A value of -1 returns an exact match or the next smaller item; 1 returns an exact match or the next larger item. A value of 2 enables wildcard matching, where an asterisk represents any number of characters and a question mark represents one character. A tilde can be used to search for a literal wildcard character.

With product codes in A2:A100, names in B2:B100, and a partial code such as AB-7 in E2, this formula returns the first name for a code containing that text: =XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2). Wildcards are useful for deliberately partial searches, but they can also match more records than expected. Use them only when a partial match is appropriate, and remember that XLOOKUP returns the first match it encounters by default.

The separate search_mode argument controls search direction or algorithm. Its default searches from first to last; -1 searches from last to first. Modes 2 and -2 use binary search and require the lookup data to be sorted in the appropriate order. If the data is not sorted correctly, binary search can return an incorrect result or fail to find a value. For ordinary unsorted lists, keep the default search mode.

Show a helpful result instead of #N/A

If a missing item is an expected outcome, provide a message with XLOOKUP’s fourth argument, if_not_found. With the lookup value in E2, product codes in A2:A100, and names in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100,"Product not found"). When no match exists, the cell displays “Product not found” instead of #N/A.

This argument handles a lookup miss; it does not repair an error in a matched return cell. If you need to handle only #N/A errors from the whole formula, you can use IFNA. With the same ranges and lookup cell, =IFNA(XLOOKUP(E2,A2:A100,B2:B100),"No result") replaces an #N/A result with “No result” while allowing other kinds of errors to remain visible.

IFERROR is broader: it replaces many error types, including #VALUE! and #REF!, as well as #N/A. Broad suppression can make a broken formula or damaged reference look like an ordinary missing record. Prefer XLOOKUP’s own missing-result argument or IFNA when the specific issue is “not found,” and investigate other errors rather than hiding them indiscriminately.

Common problems that look like a missing match

The range is too short: New rows may fall outside a fixed reference. Extend the range or use an Excel Table so references can expand as records are added.
The wrong column is searched: A formula may point to descriptions when the lookup value is an ID. Verify the selected lookup array rather than relying on column labels alone.
The search is partial when it should be exact: Wildcard mode can match an unintended item. Remove wildcard characters or return to exact matching for unique identifiers.
The source itself has duplicates: XLOOKUP returns one matching record, usually the first in the search direction. A duplicate is not the same as a missing value, but it can make the returned result seem wrong.
The return cell has an error: Trace the matching row and inspect its result cell. The lookup can be correct while the returned value is not.

Also distinguish #N/A from other Excel errors. A malformed reference can produce #REF!; incompatible calculations may produce #VALUE!; and an unsupported function name may produce #NAME?. These errors point to different problems, so replacing every error with the same text can make diagnosis harder.

Frequently Asked Questions

Why does XLOOKUP return #N/A when the value is visibly there?
Check for text-versus-number differences, hidden spaces or characters, a lookup range that misses the row, and a return cell that already contains #N/A. The displayed text may look identical without being stored identically.

How do I make XLOOKUP show blank instead of #N/A?
With the lookup value in E2, codes in A2:A100, and results in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100,""). This displays an empty string when no match is found. It does not turn a matched error in column B into a blank.

Does XLOOKUP require the lookup column to be sorted?
No, not when you use the default search mode. Sorting is required for the binary search modes 2 and -2; use those only when the data is sorted in the required order.

Can XLOOKUP match part of a text value?
Yes. Set match_mode to 2 and use wildcard characters such as * for any number of characters or ? for one character. This is a deliberate partial search, not the default exact match.

Why does my computer reject the XLOOKUP formula?
Confirm that you are using Microsoft 365 or Excel 2021 or later. In some regional settings, Excel expects semicolons instead of commas between formula arguments, even though comma-separated formulas are shown here. Older Excel versions may not support XLOOKUP.

When another lookup method may be appropriate

If a workbook must run in older Excel versions without XLOOKUP, INDEX and MATCH can provide a compatible alternative when available. VLOOKUP may also suit a straightforward table where the lookup column is to the left of the return column, but its column-index approach is easier to disrupt when columns are inserted. The best choice depends on who needs to open the workbook and how its data is arranged.

Before switching functions, verify that the original data and desired matching rule are clear. No lookup function can reliably infer whether a trailing space is accidental, whether “0042” is the same identifier as “42,” or which duplicate record should take priority. Clean and standardize the source data, decide whether matching should be exact or partial, and then choose a lookup formula that expresses that decision.

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.