IFERROR with XLOOKUP: Formula, Examples, and Fixes

Formulas & functions · Updated November 21, 2026

With the lookup value in E2, keys in A2:A5, and return values in B2:B5, use =IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Not found"). It returns the matching value, or “Not found” if the formula produces an error; for a simple missing-match message, XLOOKUP’s own optional fallback is usually enough.

How XLOOKUP and IFERROR work together

XLOOKUP searches one range or array for a value and returns a corresponding value from another range or array. IFERROR evaluates an expression and substitutes a chosen result if that expression returns an error. Wrapped together, they let you decide what a spreadsheet should display when a lookup does not succeed.

In the opening example, Excel searches A2:A5 for the value in E2 and returns the value from the same relative position in B2:B5. If E2 matches a key, the result is the corresponding entry. If the lookup formula returns an error, IFERROR displays “Not found” instead. This is useful in reports where a visible error code would confuse readers or disrupt a presentation.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. It is not available in Excel 2019 or Excel 2016. IFERROR is also available in older Excel versions, but that does not make the newer XLOOKUP function available there.

Start with XLOOKUP’s built-in not-found result

XLOOKUP has an optional fourth argument called if_not_found. It is specifically for the case where the lookup value is not found, so you often do not need to wrap XLOOKUP in IFERROR just to show a friendly missing-match message.

With E2 holding the lookup value, keys in A2:A5, and results in B2:B5, enter:

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

If E2 contains 1043 and A2:A5 contains 1041, 1042, 1043, and 1044, the formula returns the value from B4. If E2 contains 1099, which is absent from A2:A5, it returns “Not found.” XLOOKUP uses exact matching by default, so you do not need to add an exact-match setting for this usual lookup.

The fourth argument handles an unsuccessful search, not every possible error. For example, an invalid reference or an incompatible array shape can still produce an error. That distinction is the main reason to choose between XLOOKUP’s fallback and IFERROR.

When to use IFERROR around XLOOKUP

Use IFERROR when you deliberately want a fallback for any error produced by the full expression, not just for a missing lookup value. That may be appropriate in a user-facing report where the underlying formula is controlled and the desired display is the same for all expected error cases.

With E2 as the lookup value, A2:A5 as the key range, and B2:B5 as the return range, this version catches errors from the XLOOKUP expression:

=IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Check lookup")

If there is a valid match, it returns the corresponding value. If the expression returns an error, it displays “Check lookup.” That broad coverage can be convenient, but it can also hide a problem that needs fixing. A misspelled range reference, a damaged formula, or an array-size issue may all be replaced with the same reassuring-looking message.

Choose the fallback text to suit the purpose. “Not found” says the item was not located; “Check lookup” is less definitive and may better suit a formula that could fail for other reasons. Avoid a message that implies the source data is wrong unless you know that is what happened.

Concrete examples with results

Suppose a small product table has product codes in A2:A5 and product names in B2:B5. The codes are P-10, P-11, P-12, and P-13, and the corresponding names are Cable, Adapter, Stand, and Case. If E2 contains P-12, the following formula returns Stand:

With the lookup code in E2, codes in A2:A5, and names in B2:B5:

=IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Code not found")

If E2 instead contains P-99, and that code is not in A2:A5, the result is “Code not found.” The formula is useful when a user may enter a code that is not in the table.

You can also look up a value in one column and return a value from another column to its left. XLOOKUP does not require the return range to be to the right of the lookup range. With employee IDs in C2:C20, names in A2:A20, and the requested ID in E2, this returns the matching name or a message if the ID is absent:

=IFERROR(XLOOKUP(E2,C2:C20,A2:A20),"ID not found")

For the same employee data, if you only want to handle a missing ID rather than all errors, put the message in XLOOKUP’s fourth argument:

=XLOOKUP(E2,C2:C20,A2:A20,"ID not found")

In both cases a valid ID returns the name on the corresponding row. The second form makes the intended behavior clearer: only the not-found case gets that message, while other formula errors remain visible for investigation.

Returning a blank instead of an error

Some worksheets should show an empty-looking cell when no match exists. With a lookup value in E2, keys in A2:A5, and results in B2:B5, use XLOOKUP’s not-found argument:

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

The formula returns a matching result when one exists and an empty text string when no match exists. That can keep a report uncluttered, but the cell is not truly empty: it contains a formula whose displayed result is an empty string. This distinction can matter for checks such as ISBLANK and for formulas that count or process cells.

Using IFERROR with an empty string is broader. With the same layout, this formula displays an empty-looking result for any error from the XLOOKUP expression:

=IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"")

Because this can conceal issues beyond a missing match, prefer the built-in XLOOKUP fallback when blank is intended only for an absent key. Consider adding a separate status column or using a visible message during data preparation so that missing records are not silently overlooked.

IFERROR compared with IFNA

For an ordinary exact XLOOKUP with correctly sized ranges, a value that is not found normally produces the #N/A error. IFNA catches only #N/A, whereas IFERROR catches a wider range of Excel errors. If your purpose is specifically to replace an unsuccessful lookup while leaving other errors visible, IFNA can communicate that intent more precisely.

With the lookup value in E2, keys in A2:A5, and return values in B2:B5, this formula displays a message for #N/A while allowing other error types to remain visible:

=IFNA(XLOOKUP(E2,A2:A5,B2:B5),"No matching key")

For a standard not-found response, XLOOKUP’s own fourth argument is often the most direct choice. Use IFNA when you specifically need to catch #N/A in a larger expression, and use IFERROR when the design calls for replacing any error. Neither function repairs the cause of an error; they only change what the formula returns.

Common problems and how to diagnose them

  • The formula always shows the fallback. Check whether the lookup value actually exists, and compare the types and contents of the two values. A number stored as text may not behave like a numeric value, and extra spaces or different characters can prevent a match.
  • Other errors disappear too. This is expected with IFERROR. Temporarily remove the wrapper or replace it with XLOOKUP’s not-found argument to see whether the issue is a missing key or another formula problem.
  • The formula returns the wrong row. XLOOKUP defaults to exact matching, but optional match and search settings can change the search behavior. Review any fifth or sixth arguments you added, especially if you copied a formula from elsewhere.
  • The formula returns an error despite a valid key. Confirm that the lookup array and return array have compatible dimensions. For example, searching a five-row range and returning from a different-sized range can cause a problem.
  • The workbook opens on an older Excel release. XLOOKUP is unavailable in Excel 2019 and Excel 2016. IFERROR cannot compensate for a function the installed version does not recognize; use a compatible lookup method or open the workbook in a version that supports XLOOKUP.

When diagnosing a workbook, it is often safer to keep useful error information visible until the data and references are correct. Once the formula is verified, add the fallback that best describes the expected situation. In Microsoft 365 for Windows, you can inspect the formula in the formula bar and use Formulas > Evaluate Formula to follow the calculation step by step. The Mac ribbon may arrange commands differently, and Excel for the web may not expose every desktop auditing tool in the same way.

Choosing the right pattern

Use this decision guide to pick the simplest formula that matches the task:

  • Only a missing match needs a message: put the message in XLOOKUP’s fourth argument.
  • Only #N/A should be replaced: use IFNA around the expression, particularly when it is part of a larger calculation.
  • Any error should produce one approved output: use IFERROR, while making sure that hiding other errors is acceptable.
  • You need to troubleshoot: remove the wrapper or use a more specific fallback temporarily, so formula faults are not obscured.

These options are not merely different ways to write the same thing. They express different assumptions about what can go wrong. Narrow handling makes unexpected problems easier to spot; broad handling can make a polished report easier to read. Choose based on whether the formula is being used for data checking or for a finished display.

Frequently Asked Questions

Do I need IFERROR with XLOOKUP?
No. XLOOKUP can return a chosen result for a missing match through its optional fourth argument. Add IFERROR only if you want to replace other errors from the expression as well.

What is the simplest formula for “not found”?
With the lookup value in E2, keys in A2:A5, and results in B2:B5, use =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). It returns a matched result or the specified message when the value is absent.

Does IFERROR hide #N/A from XLOOKUP?
Yes. IFERROR replaces #N/A and other Excel errors with its chosen fallback. If you intend to handle only #N/A, use IFNA or XLOOKUP’s built-in not-found argument instead.

Can XLOOKUP return a blank when there is no match?
Yes. With the same layout, use =XLOOKUP(E2,A2:A5,B2:B5,""). The result looks blank, but it is an empty text value returned by a formula, not a genuinely empty cell.

Why does XLOOKUP say “not found” when the value looks correct?
Check for extra spaces, a number stored as text, or small differences in the key. Also verify that you are searching the intended range and have not added match or search settings that change the lookup behavior.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.