XLOOKUP Example With IFERROR: Excel Formula and Tips

An XLOOKUP formula wrapped in IFERROR can show a useful message instead of an Excel error: with the lookup value in E2, IDs in A2:A6, and names in B2:B6, enter =IFERROR(XLOOKUP(E2,A2:A6,B2:B6),"ID not found"). For a simple missing match, XLOOKUP already has a built-in not-found argument, so you may not need IFERROR at all.

A basic XLOOKUP and IFERROR example

Suppose a small staff list has employee IDs in A2:A6 and names in B2:B6. Cell E2 contains the ID to search for. If E2 contains 104 and that ID is on the list beside Mira Chen, the formula above returns Mira Chen. If the ID is absent, XLOOKUP normally returns the #N/A error; IFERROR replaces that error with the text ID not found.

The formula has two functions, evaluated from the inside out. XLOOKUP searches the ID range and returns the corresponding value from the name range. IFERROR then checks the result: it passes a normal result through unchanged, but substitutes its second argument if the first argument evaluates to an error.

The general structure is =IFERROR(XLOOKUP(lookup_value,lookup_array,return_array),value_if_error). The lookup array and return array should have matching dimensions, such as A2:A100 and B2:B100. XLOOKUP’s default match is exact, which is usually what you want for IDs, product codes, and names.

What each part of the formula does

In the example, E2 is the value to find, A2:A6 is where Excel searches, and B2:B6 is where Excel gets the answer. XLOOKUP compares E2 with the values in column A and returns the value from the corresponding position in column B. Unlike older lookup patterns, the return range does not have to sit to the right of the lookup range.

IFERROR has two arguments: the expression to evaluate and the value to show if that expression produces an error. Here the expression is the entire XLOOKUP calculation. The fallback can be text, a number, a blank string, or another calculation, but choose it to make the result understandable. A visible message often makes a missing record easier to investigate than an empty cell.

When you copy the formula down a column, relative references such as E2, A2:A6, and B2:B6 adjust. If the lookup ranges must stay fixed while the formula is filled down, use absolute references. With IDs in A2:A6, names in B2:B6, the lookup ID in E2, and the formula going in F2, this version keeps the ranges fixed:

=IFERROR(XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6),"ID not found")

For this layout, if E2 is 104 and the matching name in B5 is Mira Chen, F2 displays Mira Chen. The dollar signs lock the row and column references to those source ranges; E2 remains relative so it can change to E3 when copied down.

Use XLOOKUP’s built-in not-found argument

XLOOKUP includes an optional fourth argument named if_not_found. It is specifically for the case where no match is found. With IDs in A2:A6, names in B2:B6, and the search ID in E2, this formula returns a message for a missing ID without an outer IFERROR:

=XLOOKUP(E2,A2:A6,B2:B6,"ID not found")

If E2 is 999 and 999 is not in A2:A6, the result is ID not found. If E2 is 104 and the corresponding name is Mira Chen, the result is Mira Chen. This is often the clearest formula when the only error you want to handle is an ordinary no-match result.

The built-in argument and IFERROR are not identical. The if_not_found argument handles XLOOKUP’s no-match outcome, which is #N/A. IFERROR can replace a wider range of errors produced while evaluating the lookup expression, including errors caused by invalid references or incompatible range sizes. Wrapping every lookup in IFERROR may therefore hide a problem that deserves attention.

You can use both together, but it is usually unnecessary for a simple lookup. If you do, make the messages distinguishable: the XLOOKUP argument can say No matching ID, while IFERROR can report Check the formula or source ranges. That separation helps tell a normal missing record from a broken calculation.

When IFERROR is useful—and when it can mislead

IFERROR is useful when a workbook should display a controlled result instead of an error, particularly in a report or a user-facing summary. It can also be appropriate when several potential error types should lead to the same fallback. For example, a lookup built from ranges that may change as a file is refreshed could use a clear warning rather than displaying a raw error code.

However, IFERROR catches any error from its first argument, not just a missing match. If the source range has been deleted, the ranges have incompatible dimensions, or the return value itself contains an error, a broad fallback can make that issue look like an ordinary absent record. During workbook development, leaving errors visible or using a more specific message can make debugging easier.

If the only error you intend to handle is #N/A, IFNA is a narrower alternative. With lookup IDs in A2:A6, names in B2:B6, and the requested ID in E2, use:

=IFNA(XLOOKUP(E2,A2:A6,B2:B6),"No matching ID")

If E2 is not found, the formula returns No matching ID. Other error types are not converted by IFNA, so they remain visible. That distinction is valuable when a #REF! or #VALUE! would indicate a formula or data-layout problem rather than a normal missing record.

Common problems and how to troubleshoot them

A #N/A result usually means XLOOKUP did not find an exact match, but the apparent values may not actually be identical. A code stored as the number 104 may not match a code stored as text, and leading or trailing spaces can also cause a mismatch. Check the source values and their data types before adding a broader error trap.

Confirm that the lookup value is present in the lookup array, including punctuation, spaces, and leading zeros.
Check that lookup_array and return_array cover corresponding records and have the same number of rows or columns.
Look for duplicate lookup values. XLOOKUP returns the first match by default, so repeated IDs can return a result that is valid but unexpected.
Temporarily remove IFERROR while troubleshooting. The underlying Excel error can point to a bad reference or an invalid range that a custom message would conceal.

Also check that the lookup and return ranges point in the intended direction. XLOOKUP can search a column and return from a column to its left, but it still pairs entries by position. A lookup range A2:A100 paired with a return range B3:B101 is not aligned, even though both ranges contain 99 cells; the first lookup value would return the value from the wrong row.

If the result is a blank, distinguish a genuine blank in the return cell from a not-found case. A successful lookup can return an empty cell, while the fallback appears only when the lookup has no match or an error is caught. Avoid treating every blank-looking result as proof that the lookup failed.

Choosing a useful fallback value

The fallback should communicate what happened without making the worksheet harder to use. Text such as No matching ID is clear in a report. An empty string, written as two quotation marks with nothing between them, can keep a dashboard visually clean, but it can also make missing data easy to overlook. A numeric fallback such as zero is suitable only when zero has a meaningful and unambiguous interpretation in the data.

For a lookup ID in E2, source IDs in A2:A6, and names in B2:B6, this formula deliberately displays a blank when there is an error:

=IFERROR(XLOOKUP(E2,A2:A6,B2:B6),"")

If E2 is 999 and no such ID exists, the displayed result is blank. That does not mean Excel found an empty name; it means the fallback text is empty. If users need to distinguish missing records from records with blank return cells, use a visible message or a separate status column instead.

Version availability and entering the formula

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available as a built-in worksheet function in Excel 2019 or Excel 2016, so a workbook using it may show #NAME? in those older versions. IFERROR is older and widely available, but it cannot make an unsupported XLOOKUP function work.

In Excel for Microsoft 365 on Windows, select the cell where the result should appear, type the formula, and press Enter. Excel for the web also supports XLOOKUP in current Microsoft 365 environments. Excel for Mac supports the function as well; ribbon layouts and shortcut details can differ by platform, but the worksheet formula syntax is the same. When sharing a file, confirm that recipients use a version that supports XLOOKUP.

Frequently Asked Questions

Do I need IFERROR with XLOOKUP?

No. For a missing match, XLOOKUP has its own if_not_found argument. Use IFERROR when you deliberately want to catch errors beyond the no-match case or want one fallback for any error produced by the lookup calculation.

What is the difference between IFERROR and XLOOKUP’s if_not_found argument?

XLOOKUP’s argument handles the no-match #N/A result. IFERROR handles any error produced by its first argument. The built-in option is more targeted; IFERROR is broader and can hide unrelated formula problems.

How do I return a blank when XLOOKUP finds no match?

With the lookup value in E2, IDs in A2:A6, and names in B2:B6, use =XLOOKUP(E2,A2:A6,B2:B6,""). A blank fallback can make a report cleaner, but it does not distinguish a missing ID from a legitimate blank in the return data.

Why does XLOOKUP return #N/A even though the value looks present?

The values may differ because one is stored as text and the other as a number, or because of extra spaces, punctuation, or leading zeros. Check the underlying values and ensure the lookup range includes the expected record.

Does XLOOKUP with IFERROR return the last matching value?

Not by default. XLOOKUP searches from first to last unless you specify a different search mode. IFERROR only changes error handling; it does not change which duplicate match XLOOKUP selects.

Practical takeaway

For a straightforward exact lookup, start with XLOOKUP’s built-in not-found argument. Add IFERROR only when you intentionally want to replace other errors as well, and choose a fallback that will not disguise important data problems. If a formula unexpectedly displays that fallback, remove or narrow the error handling temporarily so you can see what Excel is reporting and fix the underlying cause.

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.