XLOOKUP Excel IF ERROR: Fix Missing Matches and Errors

To show a friendly result when XLOOKUP cannot find a match, use its fourth argument: with lookup value in D2, codes in A2:A4, and results in B2:B4, enter =XLOOKUP(D2,A2:A4,B2:B4,"Not found"). That argument handles a missing match; use IFERROR around XLOOKUP only when you also want to replace other formula errors.

What XLOOKUP’s error argument does

XLOOKUP searches one range or array and returns the corresponding value from another. Its fourth argument, named if_not_found, lets you specify what to return when the lookup value has no match. If you leave that argument out and there is no match, Excel returns #N/A. Providing a message, a number, or an empty string can make the result more useful to a person reading the worksheet.

The distinction matters: the fourth argument is not a general error handler. It is for the not-found case. If the formula refers to a deleted range, has incompatible array dimensions, or encounters another problem, that problem is not automatically replaced by the if_not_found value. That is why choosing between the built-in argument and IFNA or IFERROR depends on what you want to catch.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in some older perpetual versions, such as Excel 2019 and Excel 2016. If a workbook containing XLOOKUP is opened in an older version, that version may display a function-name error rather than calculate the formula.

Use the built-in if_not_found argument

Suppose A2:A4 contains product codes P-101, P-102, and P-103, while B2:B4 contains their prices, 12.5, 18, and 7.5. Cell D2 contains the code to search for. With that layout, enter:

=XLOOKUP(D2,A2:A4,B2:B4,"Not found")

If D2 contains P-102, the result is 18. If D2 contains P-999, the result is Not found. The fourth argument is optional, so the following formula would return #N/A for P-999 instead:

=XLOOKUP(D2,A2:A4,B2:B4)

For a reusable worksheet, a clear message is often better than displaying a raw error. Choose wording that tells the user what to do, such as Check product code or No matching customer. If the result feeds another calculation, returning a suitable number may be more helpful than text, because text can cause a later calculation to fail or be ignored.

XLOOKUP searches for an exact match by default, and it searches from the first item to the last by default. You do not have to add a zero for exact matching, unlike the fourth argument of VLOOKUP. The optional arguments after if_not_found control match mode and search mode, so avoid inserting a match-mode value in the fourth position by mistake: that position belongs to the not-found result.

When to use IFNA or IFERROR

Use IFNA when the specific error you want to handle is #N/A. This is useful if XLOOKUP has no separate message argument in a larger formula, or if you want to apply one consistent missing-value rule around the lookup. With the same product-code ranges and D2 lookup value, this formula returns 18 for P-102 and Not found for P-999:

=IFNA(XLOOKUP(D2,A2:A4,B2:B4),"Not found")

IFERROR is broader. It replaces any error produced by the expression inside it, not just #N/A. With the same layout, this formula also displays Not found for a missing code:

=IFERROR(XLOOKUP(D2,A2:A4,B2:B4),"Not found")

That broad behavior can hide a genuine formula problem. For example, if a return range is accidentally shortened or a reference becomes invalid, IFERROR may make the worksheet look normal while concealing the cause. Prefer XLOOKUP’s fourth argument for an ordinary missing match, or IFNA when you specifically want to catch #N/A. Use IFERROR when replacing every possible error is genuinely appropriate and the formula has been checked for underlying problems.

These functions do not repair the lookup itself. They only change what the formula displays after an error occurs. If a lookup should succeed but returns the not-found message, investigate the values and ranges rather than treating the message as proof that the data is correct.

Return a blank or another useful value

Sometimes a missing match should leave the cell visually empty. With product codes in A2:A4, corresponding prices in B2:B4, and the lookup value in D2, use:

=XLOOKUP(D2,A2:A4,B2:B4,"")

If D2 is P-999, Excel returns an empty text value, so the cell appears blank. It is not identical to a genuinely empty cell: formulas such as ISBLANK can distinguish the formula result from a cell that contains nothing. That difference matters in some data-cleaning and reporting workflows.

Also distinguish a missing match from a matching row whose return cell is empty. The fourth argument is used when there is no match; it is not a general replacement for an empty value in the return range. If blank return cells need special handling, decide whether they should display zero, a label, or remain visually blank, and build that rule deliberately. A blanket IFERROR does not solve this distinction.

For numeric data, returning zero can be appropriate if zero has a real meaning in the report. For prices, account numbers, or dates, zero may be misleading. For a dashboard, a short label such as No record may be clearer. Consider how the result will be sorted, added, charted, or used by a later formula before choosing a text value.

Diagnose common XLOOKUP errors

A friendly error result is useful, but it should not prevent you from checking why a lookup failed. Common causes include:

Extra spaces: One code may contain a leading or trailing space while the other does not. Compare the source values or clean the data before lookup.
Different data types: A number stored as text may not match a numeric value that looks identical on screen. Check whether both lookup values use the same underlying type.
Wrong lookup range: The lookup array must contain the value being searched for. XLOOKUP does not require the lookup column to be the leftmost column in a table, but the chosen lookup array still has to be correct.
Misaligned ranges: The lookup array and return array should have compatible dimensions. If one range covers rows 2 through 100 and the other rows 2 through 99, correct the range selection.
Unexpected duplicates: By default, XLOOKUP returns the first matching item it finds. If the lookup column contains duplicate keys, verify that the first match is the intended record.
Other errors: #REF! can indicate an invalid reference, while #VALUE! can point to incompatible array dimensions or arguments. A catch-all IFERROR can conceal these issues instead of fixing them.

To check a suspected mismatch, compare the actual source values, not only how they look on screen. Number formatting can make a numeric value appear like text, and hidden spaces can be difficult to notice. If the worksheet is meant to have unique keys, remove or resolve duplicates rather than relying on the first-match behavior.

Understand match and search behavior

With its defaults, XLOOKUP uses exact matching and searches from the first entry downward through the lookup array. For most code, name, and ID lookups, those defaults are appropriate. Approximate matching is available through the optional match_mode argument, and reverse searching is available through search_mode. These options change how XLOOKUP finds a result; they do not change what the if_not_found argument means.

For example, approximate matching may be useful for a sorted tax or commission threshold table, but it is not a safe substitute for exact matching on product IDs. If the data is unsorted or the selected mode is wrong, a formula may return a plausible but incorrect value instead of #N/A. Test the lookup against known inputs and confirm that the table order meets the requirements of the chosen mode.

If you expect the last matching record rather than the first, change the search mode to search from last to first. This can help with a log where later rows represent more recent entries, provided the data is arranged and maintained consistently. It does not automatically determine which record is newest by date; the search order must actually correspond to the intended rule.

Alternatives for older Excel versions

If the workbook must work in Excel versions without XLOOKUP, INDEX with MATCH is a common alternative. With product codes in A2:A4, prices in B2:B4, and the lookup value in D2, enter:

=IFNA(INDEX(B2:B4,MATCH(D2,A2:A4,0)),"Not found")

For P-102, MATCH finds its position in the code range and INDEX returns the value in the corresponding position of the price range, which is 18. For P-999, MATCH returns #N/A and IFNA displays Not found. The zero in MATCH requests an exact match. This approach is more verbose, but it works in older Excel releases that support INDEX, MATCH, and IFNA.

VLOOKUP is another older option when the lookup column is the first column of the selected table and the result is to its right. It is less flexible when the return column is on the left, and its column-number argument can become fragile if columns are inserted or rearranged. Choose based on the oldest Excel version that must open the workbook and the structure of the data, not just on which formula looks shortest.

Frequently Asked Questions

Does XLOOKUP have an IFERROR option?
XLOOKUP has a built-in fourth argument called if_not_found, which handles a missing match. It is not the same as IFERROR, because it does not replace every possible formula error. Wrap XLOOKUP in IFERROR only if you intend to replace other error types too.

How do I make XLOOKUP return blank if there is no match?
Put an empty text string in the fourth argument. With D2 as the lookup value, A2:A4 as the lookup range, and B2:B4 as the return range, use =XLOOKUP(D2,A2:A4,B2:B4,""). This produces a visually blank result for a missing match.

Why does XLOOKUP still show an error?
The error may not be a missing-match #N/A. Check for invalid references, mismatched array sizes, or errors already present in the source data. The fourth argument handles not-found results, not every possible error in the formula.

Should I use IFNA or IFERROR with XLOOKUP?
Use IFNA if you want to replace only #N/A. Use IFERROR if you deliberately want to replace all errors from the lookup expression. For a straightforward missing-match message, XLOOKUP’s own if_not_found argument is usually the clearest option.

Does XLOOKUP return the first or last match?
By default, it returns the first match because it searches from the beginning of the lookup array. Its optional search_mode can search from the end instead. If duplicate values are possible, make sure the selected direction matches the record you mean to retrieve.

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.