Yes—use IFERROR around XLOOKUP when you want to replace an error with a message or another value. With the requested code in E2, codes in A2:A5, product names in B2:B5, and E2 set to 120 while A2:A5 contains 104, 108, 112, and 116, use =IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Not found"); it returns Not found because 120 is not in the lookup range. For a simple missing-match message, however, XLOOKUP already has a built-in option that is often the better choice.
What IFERROR does with XLOOKUP
IFERROR checks the result of an expression. If that result is an Excel error, it returns the fallback value you provide; if there is no error, it returns the original result unchanged. In the opening example, XLOOKUP cannot find 120, so it normally returns #N/A. IFERROR intercepts that error and displays Not found instead.
This can make a report easier to read, especially when users should not have to interpret Excel error codes. The fallback can be text, a number, a blank string, or another calculation. It does not repair the underlying lookup, though. It changes what appears in the cell, so use a fallback that helps users understand whether a result is missing or whether something else needs attention.
IFERROR is broad: it handles errors such as #N/A, #VALUE!, #REF!, #DIV/0!, and #NAME?. That breadth is useful when a formula can fail in several ways, but it can also conceal a problem you would rather find and fix.
XLOOKUP’s built-in not-found argument
XLOOKUP includes an optional argument for what to return when it cannot find the lookup value. It is the fourth argument, after the lookup value, lookup array, and return array. If that argument is omitted, an unsuccessful search normally produces #N/A. Supplying a message handles the ordinary “no match” situation without wrapping the function in another function.
With the requested code in E2, codes in A2:A5, names in B2:B5, and E2 containing 120 while the code range contains 104, 108, 112, and 116, use =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). The result is Not found. If E2 is 108 and B2:B5 contains Widget A, Widget B, Widget C, and Widget D in that order, the same formula returns Widget B.
For a missing match, this built-in argument is usually clearer than IFERROR because it states exactly which outcome you are handling. It handles XLOOKUP’s no-match result, rather than disguising every possible error that might arise while evaluating the formula.
When wrapping XLOOKUP in IFERROR makes sense
Use IFERROR when you deliberately want one fallback for any error in the lookup expression, not just for a missing key. For example, an error might come from a damaged reference or from an error value in the matched return cell. IFERROR can prevent that error from appearing in a summary, although you should consider whether hiding it is appropriate.
With product codes in A2:A5, prices in B2:B5, and the requested code in E2, where E2 is 108 and B2:B5 contains 12, 18, 22, and 25, use =IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Check source data"). The formula returns 18 because the lookup succeeds. If the lookup expression instead evaluates to an error, the fallback is Check source data.
Notice that the formula above does not provide a separate no-match message: a missing code also results in the IFERROR fallback. If you want distinct messages for a missing code and other errors, provide XLOOKUP’s not-found argument inside IFERROR. With the same layout but E2 set to 120, use =IFERROR(XLOOKUP(E2,A2:A5,B2:B5,"Not found"),"Check source data"). It returns Not found for the missing code; the outer fallback is reserved for another error that escapes XLOOKUP’s not-found handling.
Choosing the right fallback
The fallback is part of the meaning of the report, not just a cosmetic setting. Choose wording that distinguishes a normal absence from a data or formula problem. “Not found” may be suitable for a product search; “Check source data” signals that someone may need to investigate. Avoid a generic blank if users could mistake it for a valid empty result.
- A readable message: Use a short label such as Not found when a missing match is expected and should be visible.
- A blank display: Return an empty string when missing results should not clutter a presentation, but document that blank cells can mean “no match.”
- A numeric fallback: Use a number only when it has a defensible meaning in later calculations. Replacing a missing price with zero, for example, can make totals look lower than they should.
- A diagnostic message: Use language such as Check source data when you intentionally catch more than a simple missing match.
To display a blank for an unmatched code, use the same code-and-name layout as the earlier example, with E2 set to 120, and enter =XLOOKUP(E2,A2:A5,B2:B5,""). The result is an empty-looking cell. That does not mean the formula found a blank product name; it means the lookup did not find the code and the fourth argument supplied an empty string.
Common problems and misconceptions
IFERROR can hide errors unrelated to a missing match. If a workbook suddenly starts showing a fallback everywhere, check the source ranges and references instead of assuming all keys are absent. A formula that conceals a broken reference may make a report look tidy while leaving its data unreliable.
A found row can still contain an error. XLOOKUP’s not-found argument is for an unsuccessful search. It is not a universal error handler for every value returned from the matched row. If the matched return cell itself contains an error, IFERROR around the entire lookup can catch that error; whether it should do so depends on whether the error is expected or should be corrected.
Check the lookup and return ranges. XLOOKUP expects the lookup and return arrays to correspond in size and orientation. A vertical lookup range should normally pair with a return range covering the same rows; a horizontal lookup range should pair with a corresponding horizontal return range. Incompatible dimensions can cause an error, and IFERROR may mask it rather than fix it.
Exact match is the default. XLOOKUP searches for an exact match unless you choose a different match mode. Before adding approximate matching, check whether the source values have consistent types and formatting. A number stored as text, an extra space in a code, or a genuinely different value can prevent an exact match even when two entries look similar on screen.
Do not treat every error as “not found.” A missing identifier and a broken formula are different situations. If a workbook is used for operational decisions, preserving that distinction can make review and troubleshooting much easier.
IFERROR compared with IFNA
If the only error you want to replace is #N/A, IFNA is a narrower alternative. It leaves other kinds of errors visible, which can help expose problems that IFERROR would hide. XLOOKUP’s built-in not-found argument is often the most direct way to handle a missing match; IFNA is useful when you need to handle an #N/A from a larger expression or want to be explicit about that particular error.
With E2 containing 120, codes 104, 108, 112, and 116 in A2:A5, and their names in B2:B5, use =IFNA(XLOOKUP(E2,A2:A5,B2:B5),"Not found"). The result is Not found because the unsuccessful lookup returns #N/A. If another kind of error occurs, IFNA does not replace it, whereas IFERROR would.
In short, choose the handling that matches the problem: use XLOOKUP’s fourth argument for an ordinary no-match result, IFNA to catch #N/A specifically, or IFERROR when you intentionally want to replace any error from the complete expression.
Excel version and platform notes
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Mac and Excel for the web. The formulas in this article use commas as argument separators, as is standard in many regional settings. Excel installations configured for other list separators may require semicolons instead; that is a regional setting difference, not a different function.
Excel 2019 and earlier do not include XLOOKUP. If a workbook using it is opened in one of those versions, the function may appear as an unsupported function and fail to calculate there. If the workbook must support older Excel, consider a compatible lookup method such as INDEX and MATCH, or keep a version of the file that does not rely on XLOOKUP. IFERROR itself is available in older Excel releases, but it cannot make an unsupported XLOOKUP function available.
Frequently Asked Questions
Can I use IFERROR with XLOOKUP?
Yes. Put the complete XLOOKUP expression inside IFERROR and supply the value to display if it returns an error. For a missing-match message alone, XLOOKUP’s fourth argument is usually simpler.
Is IFERROR necessary when XLOOKUP does not find a match?
No. XLOOKUP’s optional fourth argument lets you specify what to return when no match exists. IFERROR is useful when you also want to catch other errors from the lookup expression.
Will XLOOKUP’s not-found argument catch every error?
No. It handles a lookup that finds no match. Other errors, such as a problem in a reference or an error in the matched return value, may need investigation or a separate handler such as IFERROR.
Should I use IFERROR or IFNA with XLOOKUP?
Use IFNA if you want to replace only #N/A and leave other errors visible. Use IFERROR when you intentionally want one fallback for any error in the expression. For a routine missing match, the built-in not-found argument is often the clearest option.
Why does XLOOKUP return Not found when the value appears to be there?
Check that the lookup value and source value are truly identical. A number stored as text, leading or trailing spaces, a typo, or searching the wrong range can prevent an exact match. Also confirm that the lookup and return arrays cover the intended rows.
Related
- Python Pandas Excel to CSV: Steps, Options, and Fixes
- Python Pandas Excel to Dictionary: Practical Guide
- Python pandas Excel Formatting: A Practical Guide to Styles
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- IRR Formula Not Working in Excel? Causes and FixesThe IRR formula usually fails because the cash-flow range is incomplete or contains no valid positive-and-negative combination, the…
- Can’t Insert a Row in Excel? Fix the Common CausesIf you can’t insert a row in Excel, first select the row number where the new row should…
- Excel Spreadsheet Hyperlinks Not Working? Fixes to TryIf Excel spreadsheet hyperlinks are not working, first check that the link’s address is correct and that the…
- Excel VLOOKUP Not Working? Fix the Common CausesIf VLOOKUP is returning an error or the wrong result, check that the lookup value is in the…
- Flash Fill Error in Excel? Causes and FixesA Flash Fill error in Excel usually means the feature did not recognize your example pattern, is turned…
- Outlook Block Sender Not Working? Causes and FixesIf you couldn’t block a sender in Outlook, first make sure you selected an email in the correct…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.