Excel XLOOKUP #N/A Error: Causes and Fixes

Errors & fixes · Updated November 19, 2026

If XLOOKUP returns #N/A, Excel usually cannot find the lookup value in the lookup range. With the value to find in E2, IDs in A2:A100, and names in B2:B100, add a not-found result as the fourth argument: =XLOOKUP(E2,A2:A100,B2:B100,"Not found"). If the value should be present, check for mismatched data, extra spaces, or an incorrect range before masking the error.

What an XLOOKUP #N/A error means

XLOOKUP searches a lookup array for a value and returns the corresponding item from a return array. By default, it looks for an exact match. If it cannot find one, it returns #N/A—Excel’s way of indicating that a requested value is not available. For example, a search for employee ID 1047 will fail if the lookup range contains 1046 and 1048 but not 1047.

The function’s syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The optional if_not_found argument lets you choose what appears when there is no match. If you leave it out, Excel displays #N/A. This result is different from an empty cell in the return range: if a match exists but its corresponding return cell is empty, the lookup has succeeded, even though the displayed result may look blank or like zero depending on the range and formula context.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not included in Excel 2019 or Excel 2016, where opening a workbook that contains it may show #NAME? rather than a not-found result. That distinction matters: #N/A usually concerns a lookup that did not find a match, while an unsupported function is a compatibility issue.

Use the not-found argument when a missing result is expected

If an absent value is a normal outcome—such as a product code that has not been added to a price list—return a clear message rather than an error. With the lookup value in E2, codes in A2:A100, and prices in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100,"Code not listed"). The message appears only when XLOOKUP finds no match; a successful lookup still returns the corresponding price.

You can also return an empty-looking result when that suits a report. With the same layout, use =XLOOKUP(E2,A2:A100,B2:B100,""). This displays a blank string when no code is found, but it does not create a genuinely empty cell. That can affect later calculations, tests, or charts, so a descriptive message is often better while you are checking or maintaining the workbook.

Choose the fallback based on what the result means:

  • Use a message such as Not found when someone needs to know that the key is missing.
  • Use an empty string when an absent result should not clutter a presentation, and downstream formulas can handle it.
  • Use a numeric value only when treating a missing match as that number is logically correct. Do not substitute zero for a missing price or quantity just to suppress an error.

Check the lookup value and ranges first

When a value should exist but XLOOKUP still returns #N/A, verify the inputs before changing the formula. A simple typo, an accidental extra character, or a range that stops one row too early can explain the result. In a table that grows over time, fixed ranges may also omit newer records; structured table references can help keep the lookup range aligned as rows are added.

  1. Confirm that the lookup value is the value you intend to search for. Check the source cell for a typo, an unexpected formula result, or a hidden space.
  2. Inspect the lookup array itself. Make sure it contains the expected key and includes the relevant rows or columns.
  3. Confirm that the return array corresponds to the lookup array: a vertical lookup range should normally be paired with a return range covering the same rows.
  4. Check whether the formula was copied and its references shifted. Use dollar signs for ranges that need to stay fixed when filling a formula down.
  5. Temporarily compare the source values directly, or filter the lookup column for the key, to determine whether a real match exists.

For example, with the search key in E2 and the candidate keys in A2:A100, =COUNTIF(A2:A100,E2) returns zero if no exact occurrence is counted and a positive number if the key appears. This check is useful, but it does not diagnose every data-quality issue: it may not reveal why visually similar entries differ, and criteria containing wildcard characters can have special behavior.

Look for text, number, and whitespace mismatches

Two values can look identical on screen but be stored differently. A product code such as 00125 may be text in one range and the number 125 in another. Leading zeros can be meaningful in identifiers, so converting everything to numbers may destroy information. Decide whether the key is an identifier or a quantity, then store both sides consistently. For identifiers, text is often appropriate; for quantities, use numeric values.

Spaces are another common cause. A value may contain a leading or trailing ordinary space, or a nonbreaking space copied from a web page. With the questionable text in A2, =TRIM(A2) removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every invisible character: TRIM does not remove the nonbreaking space character often found in imported text. Cleaning the source data or using a carefully prepared helper column is safer than repeatedly patching a lookup formula.

To identify an apparent mismatch, compare the lengths as well as the displayed text. With the suspected value in A2, =LEN(A2) counts its characters; a longer-than-expected result can point to hidden spaces or characters. If the data comes from an export, check how the export represents IDs and whether both columns were imported using the same data type. Avoid applying VALUE blindly: it converts text that represents a number, but it is unsuitable for codes where leading zeros matter and can fail on nonnumeric text.

Understand exact, approximate, and wildcard matching

XLOOKUP uses exact matching by default, so you do not need to specify a match mode for a normal ID, name, or code lookup. The optional match mode changes the search rule: 0 means exact match, -1 means exact or next smaller item, 1 means exact or next larger item, and 2 enables wildcard matching. Approximate matching can be useful for thresholds or bands, but it does not mean Excel will choose the closest value in every situation. If no eligible match exists under the selected rule, the result can still be #N/A.

Wildcard mode is useful when you intentionally want a partial text match. With a fragment in E2, text to search in A2:A100, and results in B2:B100, use =XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2). The asterisk represents any number of characters. Wildcard searching is not a fix for a misspelled exact key: it can return a different record whose text happens to contain the fragment. Use it only when partial matching is the intended rule.

Search mode controls where and in what direction XLOOKUP searches. Its ordinary search proceeds from the first item to the last; reverse search is available when you want the last matching item. Binary search modes require the lookup range to be sorted appropriately. If the data is not sorted as required, a binary search can produce incorrect results, not just a convenient alternative to exact matching. For most everyday lookups, leave the search mode at its default.

Separate a missing match from other formula problems

Not every error displayed near an XLOOKUP means that the lookup key is absent. A misspelled function name or a version that does not support XLOOKUP can produce #NAME?. A broken reference can produce #REF!. If XLOOKUP finds a match but the corresponding return cell itself contains an error, that error may be passed through to the result. Look at the matched row and the exact error type before choosing a remedy.

Also check that the lookup and return arrays have compatible dimensions and direction. For a vertical search, use ranges that cover the same set of rows; for a horizontal search, use ranges covering the same set of columns. XLOOKUP can search vertically or horizontally, but pairing unrelated ranges can return the wrong item or produce an error. If your lookup range is a formula-generated array, check that it is producing the values and shape you expect.

When you need to distinguish a missing item from a different error, consider using if_not_found rather than wrapping the entire formula in a broad error handler. That keeps the fallback focused on the no-match case and makes other problems easier to notice.

IFNA versus IFERROR for XLOOKUP

IFNA replaces an #N/A result, while IFERROR replaces a wider set of errors, including errors that may indicate a broken reference or an invalid calculation. With the key in E2, IDs in A2:A100, and names in B2:B100, =IFNA(XLOOKUP(E2,A2:A100,B2:B100),"Not found") is an alternative way to show a message for #N/A. In a straightforward lookup, putting the message in XLOOKUP’s fourth argument is usually simpler.

With those same ranges, =IFERROR(XLOOKUP(E2,A2:A100,B2:B100),"Check lookup") can conceal more than a missing match. For example, if a source formula has a reference problem, the message may hide it and make the workbook appear healthy. Use IFERROR only when every error it catches should genuinely be treated the same way; otherwise, handle the expected not-found case specifically and investigate unexpected errors.

Frequently Asked Questions

Why does XLOOKUP return #N/A when the value is visibly there?
It may not be an exact match under the surface. Check for text-versus-number differences, leading zeros, trailing spaces, nonbreaking spaces, or a range that excludes the visible row. Also verify the lookup value and the formula’s referenced cells.

How do I make XLOOKUP show blank instead of #N/A?
With the lookup key in E2, keys in A2:A100, and results in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100,""). It returns an empty string when there is no match. Remember that an empty string is not the same thing as a truly blank cell.

Does XLOOKUP need an exact-match setting?
No. Exact match is the default, so a standard lookup does not require a match-mode argument. The optional match mode is for special cases such as approximate or wildcard matching.

Can XLOOKUP return #N/A if the result cell is blank?
A blank return cell is not the same as a missing lookup key. If XLOOKUP finds the key, it returns the corresponding value; how a blank is displayed can depend on the formula context. Confirm whether the key was found and inspect the matched return cell.

Why does XLOOKUP show #NAME? instead of #N/A?
#NAME? can mean Excel does not recognize the function, including when the workbook is opened in Excel 2019 or Excel 2016. XLOOKUP requires Excel 2021 or Microsoft 365. Use a supported version or replace the formula with a compatible lookup method.

Is XLOOKUP available in Excel for Mac or Excel for the web?
XLOOKUP is available in Microsoft 365 versions of Excel for Mac and Excel for the web, as well as supported Windows versions. Availability depends on the Excel version, not simply the operating system; older perpetual releases may not include the function.

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.