XLOOKUP If Not Found Not Working? How to Fix It

If XLOOKUP is not showing your “not found” message, first check that the message is supplied as the formula’s fourth argument, if_not_found. If the formula still returns an unexpected result, the lookup value may actually match, or the result may be a different error that this argument does not handle.

What the if_not_found argument does

XLOOKUP looks for a value in one range and returns the item at the corresponding position in another range. Its fourth argument, if_not_found, tells Excel what to show when it cannot find the lookup value. If you leave that argument out, XLOOKUP returns #N/A when there is no match. Adding a fallback message replaces that particular result; it does not repair other problems in the formula.

For example, with product codes in A2:A5, prices in B2:B5, and the code to find in D2, use:

With product codes in A2:A5, prices in B2:B5, and the search code in D2, this formula returns the matching price or the text “Not found” if there is no match:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found")

The ranges in this example are illustrative: if D2 contains a code found in A2:A5, Excel returns the price from the same row in B2:B5. If D2 contains a code absent from A2:A5, it returns “Not found.” The fourth argument is optional, so its position matters. Put the fallback after the return range, not where the lookup range or return range belongs.

You can also use a blank-looking result when there is no match. With the same cell layout, this returns an empty text value if the code is absent:

With product codes in A2:A5, prices in B2:B5, and the search code in D2, this formula displays an empty result for an absent code:

=XLOOKUP(D2,A2:A5,B2:B5,"")

An empty result can make a report easier to read, but it can also hide missing data. While diagnosing a formula, use a clear message such as “Not found” so you can distinguish a missing match from a genuinely blank-looking cell.

Check the formula arguments and order

A frequent cause is putting the fallback in the wrong argument. The first argument is the value to search for; the second is the lookup range; the third is the range to return from; the fourth is the not-found result. For instance, a formula with “Not found” as the third argument would ask Excel to return that text for every match, rather than use it only when no match exists.

Check the formula bar for missing commas, quotation marks, or parentheses. Text messages need straight double quotation marks around them. Also check for a formula that ends after the return range: that is valid, but it means the fallback was omitted and an unmatched search will produce #N/A. In some regional settings, Excel uses semicolons between arguments instead of commas. If Excel rejects a formula copied from a comma-based example, your regional list separator may be the reason.

For an exact match, XLOOKUP uses exact matching by default. You can make that choice explicit by supplying 0 as the fifth argument. With the same layout, the formula below searches for an exact code and returns “Not found” if there is no exact match:

With product codes in A2:A5, prices in B2:B5, and the search code in D2, this formula requires an exact match:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found",0)

Confirm that the lookup value truly has no match

Sometimes the fallback appears not to work because Excel has found a value that looks like the intended result. This can happen when the lookup range contains duplicates. XLOOKUP normally searches from the first item to the last and returns the first match, so it will not use the fallback merely because a later row has a different result. Check for repeated keys and decide whether the first match is the one you want.

Other near-matches can be hard to see on screen. A code may contain a leading or trailing space, a nonbreaking space copied from a web page, or an invisible character. Text can also differ in punctuation, spelling, or leading zeros. For example, the text code “0042” is not necessarily equivalent to the number 42. Cell formatting can make a numeric value appear to have leading zeros without changing the underlying value, while a code imported as text may store those zeros as part of the value.

Inspect both the lookup cell and a suspected matching cell in the formula bar. If the data is imported, clean or standardize the source and search values consistently rather than applying a quick conversion to only one side. The TRIM function can remove ordinary leading and trailing spaces from text, but it does not remove every possible invisible character. For nonbreaking spaces, a deliberate cleanup step may be needed, such as replacing that character in the source data. Do not change codes casually: leading zeros and punctuation may be meaningful identifiers.

Make sure the lookup and return ranges line up

The lookup range and return range should have compatible dimensions and matching row positions. If codes are in A2:A100, the associated values might be in B2:B100. If you accidentally select A2:A100 but B3:B101, the formula can return a value from the wrong row even though it finds the code. Check the starting and ending rows of both ranges, especially after copying a formula down or editing a table.

For a horizontal lookup, both ranges should run across corresponding columns. XLOOKUP can return values from a range to the left or right of the lookup range, unlike the traditional VLOOKUP setup, but that flexibility does not remove the need for aligned data. If the lookup range and return range have incompatible shapes, Excel may return an error rather than the not-found result. A fallback only handles an unsuccessful lookup; it cannot correct broken references or a mismatched range design.

When formulas are copied, relative references can shift. If every row should search the same fixed lookup table, consider locking those ranges with dollar signs. With codes in A2:A100, prices in B2:B100, and a search value in D2, this formula fixes the table ranges so they do not shift when copied:

With the lookup table in A2:B100 and the search value in D2, this formula keeps the search and return ranges fixed when copied:

=XLOOKUP(D2,$A$2:$A$100,$B$2:$B$100,"Not found")

Identify whether the result is really a not-found case

The fourth argument is not a general error handler. If the lookup range or return range contains a problematic reference, or the formula itself has an invalid reference, Excel may show an error such as #REF! or #VALUE!. If a matching row exists but its return cell contains an error, that error can also be returned. In these situations, “Not found” is not the appropriate result because the issue is not simply that the lookup value is absent.

Use the displayed error as a clue. #N/A commonly indicates that XLOOKUP did not find a match when no fallback was supplied, but it can also come from another part of a formula. #NAME? may indicate that the Excel version does not recognize XLOOKUP or that the function name is mistyped. #REF! points toward an invalid reference. Trace the ranges and inspect the formula before wrapping it in a broad error handler.

IFNA can provide a fallback for an #N/A result, including when you are working with a lookup formula that does not have its own not-found argument. With codes in A2:A5, prices in B2:B5, and a search code in D2, this returns “Not found” for an #N/A result:

With product codes in A2:A5, prices in B2:B5, and the search code in D2, this formula substitutes a message only for an #N/A result:

=IFNA(XLOOKUP(D2,A2:A5,B2:B5),"Not found")

By contrast, IFERROR catches a wider range of errors. That may conceal a broken reference or another problem you should fix. Prefer XLOOKUP’s own fourth argument or IFNA when your intention is specifically to handle a missing match.

Separate a missing match from a blank return cell

A matched row can have an empty cell in the return range. That is different from XLOOKUP failing to find the lookup value. The formula can find the code successfully and return the corresponding empty cell, so the not-found message will not appear. This is a common source of confusion in tables with incomplete data.

To diagnose it, temporarily use a visible fallback such as “No matching code” and check whether the key exists. If it does, inspect the return cell in that row. If your report needs to distinguish a missing key from an empty result, add a separate check or label the source data so those states are not visually identical. Avoid assuming that a blank-looking result proves the lookup failed.

Likewise, a formula that returns "" for a missing match produces an empty text value; it is not the same as returning the number zero or leaving a cell genuinely unused. Other formulas that refer to the result may treat those cases differently. Choose the fallback to suit the next step in your workbook, and use a visible diagnostic message while troubleshooting.

Check match modes, search direction, and Excel version

The optional fifth argument controls the match mode. The default is exact match; other settings allow an exact match or the next smaller or larger item, and a wildcard match. If you choose an approximate or wildcard mode unintentionally, a value you expected to be “not found” may instead produce a result. Review the fifth argument if the returned row seems surprising. Wildcard matching is useful for patterns, but it can match more than one text value that resembles the search pattern.

The optional sixth argument controls search direction or binary search. The default searches from the first item toward the last. A reverse search can find the last match instead, which is helpful when a key occurs more than once. Binary search modes require the lookup data to be sorted in the specified order; using them on unsorted data can produce incorrect results. If you do not specifically need a different match mode or search direction, leave those optional arguments out and keep the formula simpler.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available as a native function in Excel 2019 and earlier. A workbook containing XLOOKUP may display #NAME? or a compatibility-related issue when opened in an older version. If that is the situation, update Excel or use a compatible alternative such as INDEX and MATCH. Excel for the web also supports XLOOKUP; menu and ribbon differences are not normally relevant to entering the formula. The instructions here assume Excel for Microsoft 365 on Windows.

A practical troubleshooting order

Work through these checks in order, changing one thing at a time. That makes it easier to identify the real cause instead of masking it with a general error formula.

Confirm the formula includes the fallback as the fourth argument and that the message is enclosed in quotation marks.
Check whether the displayed result is #N/A, another error, a blank, or a value from an unexpected row.
Verify that the search value and lookup range use the same underlying data type and contain the same characters.
Check for duplicates, extra spaces, leading zeros, and invisible characters in the lookup data.
Confirm the lookup and return ranges cover corresponding rows or columns and have not shifted when copied.
Review any fifth or sixth argument for an unintended match mode or search direction.
Confirm that the Excel version supports XLOOKUP if the function itself is not recognized.

This order separates formula setup from data quality and version issues. For example, changing “Not found” to an empty string may make a sheet look cleaner, but it cannot resolve a text-versus-number mismatch. Likewise, replacing the formula with IFERROR may hide the visible error while leaving the faulty range reference in place.

Frequently Asked Questions

Why does XLOOKUP still show #N/A?
If you see #N/A, check whether the fourth argument is present and whether the formula’s actual argument separators match your regional settings. If it is present, verify that the value truly has no exact match. Hidden spaces, data types, and leading zeros commonly cause apparent matches to fail.

Can XLOOKUP return a blank if nothing is found?
Yes. Put "" in the fourth argument to return an empty text value for an unmatched search. Use a visible message while diagnosing the formula, because a blank result can also come from a matching row whose return cell is empty.

Why does XLOOKUP return a value instead of my not-found message?
Excel has found a match, possibly an unintended one. Look for duplicate keys, wildcard or approximate match settings, and values that look alike but are not identical. XLOOKUP returns the first match by default, so a duplicate can explain why it chooses an unexpected row.

Does XLOOKUP’s not-found argument catch every error?
No. It supplies a result when XLOOKUP cannot find the lookup value. It is not a general replacement for IFERROR and does not fix invalid references, mismatched ranges, or errors in the return data. Use the specific error to diagnose the problem.

Why does XLOOKUP show #NAME?
Check the function spelling and confirm the workbook is open in Excel 2021 or Microsoft 365. Older versions such as Excel 2019 do not include XLOOKUP as a native function, so they may not recognize it.

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.