ISERROR with VLOOKUP: How to Handle Lookup Errors

Formulas & functions · Updated November 3, 2026

Yes. In Excel, ISERROR can test a VLOOKUP result and report whether it returns an error; wrap the test in IF if you want a message instead of TRUE or FALSE. For a missing-match message, a common option is IFERROR, which is shorter and easier to maintain.

What ISERROR does with VLOOKUP

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. If it cannot find an exact match, it normally returns #N/A. It can also return other errors, such as #REF! if its column number points outside the selected range. ISERROR checks the result and returns TRUE for any Excel error value, or FALSE if the result is not an error.

With product IDs in A2:A5, product names in B2:B5, prices in C2:C5, and the ID to search for in E2, this formula tests whether the price lookup returns an error:

=ISERROR(VLOOKUP(E2,$A$2:$C$5,3,FALSE))

If E2 contains P-103 and that product’s price is 8, the result is FALSE. If E2 contains P-999, which is absent from the table, the result is TRUE because VLOOKUP returns #N/A. The formula does not replace the error with a message; it only evaluates it.

Show a useful result instead of TRUE or FALSE

To display a readable response, put the ISERROR test inside IF. Using the same product table and lookup cell, this formula returns the price when the lookup succeeds and “No match” when it returns an error:

=IF(ISERROR(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),"No match",VLOOKUP(E2,$A$2:$C$5,3,FALSE))

For E2 equal to P-103, the formula returns 8. For E2 equal to P-999, it returns No match. The IF function evaluates the test first: when ISERROR is TRUE, it selects the second argument; when it is FALSE, it selects the third argument.

This construction is useful when you specifically want an error test, but it repeats the VLOOKUP expression. In a large workbook, repeated expressions can make formulas harder to read and may require Excel to perform the lookup twice. For ordinary error handling, IFERROR is usually more concise.

Use IFERROR for the usual missing-match message

IFERROR evaluates an expression and returns a chosen alternative if that expression produces an error. With the same IDs in A2:A5, prices in C2:C5, and lookup key in E2, this formula returns the price or a message:

=IFERROR(VLOOKUP(E2,$A$2:$C$5,3,FALSE),"No match")

It returns 8 for P-103 and No match for P-999. IFERROR catches more than a missing match: it also replaces errors such as #REF! and #VALUE!. That broad behavior is convenient when the fallback is appropriate for any error, but it can conceal a broken formula. If a wrong column number or damaged reference should be investigated rather than hidden, choose a narrower test or let the error remain visible.

IFERROR is available in current Microsoft Excel, including Microsoft 365. It can also be nested around other calculations, but the fallback should describe what the formula genuinely means. For example, “No match” is misleading if the lookup actually failed because the return-column index is invalid.

When ISNA is a better choice

VLOOKUP’s most familiar error for an absent exact match is #N/A. If the goal is specifically to handle that condition while allowing other errors to remain visible, ISNA is a more targeted test than ISERROR. With product IDs in A2:A5, prices in C2:C5, and a lookup key in E2, use:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),"Not found",VLOOKUP(E2,$A$2:$C$5,3,FALSE))

This returns Not found when VLOOKUP returns #N/A, and returns the price when it finds a match. An unrelated error, such as #REF! from an invalid column index, is not treated as “not found”; it remains visible. That distinction can make troubleshooting easier.

ISERROR and ISNA are not interchangeable. ISERROR catches every Excel error value, whereas ISNA catches only #N/A. ISERR is another related function: it detects errors except #N/A. For a typical VLOOKUP that uses exact matching, many users pick IFERROR for brevity, ISNA when only a missing match should be handled, and ISERROR when all error types should trigger the same response.

Make VLOOKUP return the match you intend

Error handling cannot fix a lookup configured to search incorrectly. The final VLOOKUP argument matters: FALSE requests an exact match. If you omit that argument, VLOOKUP uses approximate matching, which can return an unexpected result when the first column is not sorted as required. A result that looks plausible may not produce an error at all, so ISERROR cannot flag it.

Also check the structure of the lookup range. VLOOKUP searches only the leftmost column of that range, and the column number counts from the range’s left edge. In the example range A2:C5, 1 means column A, 2 means B, and 3 means C. If you insert or remove columns, confirm that the requested return column is still correct.

Before adding an error wrapper, check these common causes:

  • The lookup value and table key may differ because of extra spaces, different text, or a number stored as text.
  • The lookup range may not include the key or the requested return column.
  • The column index may be larger than the number of columns in the selected range.
  • The formula may be using approximate matching when exact matching is intended.
  • The key may genuinely be absent or duplicated. VLOOKUP returns the first matching row it encounters.

For a missing key, displaying “No match” can be helpful. For a type mismatch, however, hiding the error may make the spreadsheet appear complete while producing an incomplete report. Verify the lookup data before deciding what the fallback should say.

Choose a fallback that preserves useful information

A fallback is part of the spreadsheet’s meaning, not just its appearance. A blank result can keep a report tidy, but readers may not know whether the value is genuinely blank, not applicable, or missing. A clear label such as “Not found” is often easier to interpret. In a calculation, returning zero may be appropriate only when zero is a valid value and will not be mistaken for a real measurement.

For example, with IDs in A2:A5, prices in C2:C5, and a search key in E2, this formula displays a blank when any error occurs:

=IFERROR(VLOOKUP(E2,$A$2:$C$5,3,FALSE),"")

For P-999, the displayed result is an empty string. It may look like an empty cell, but it is still a formula cell, not a truly empty cell. This can matter to later formulas, filters, and checks for blank cells. If users need to distinguish missing data from an intentionally blank result, use an explicit message instead.

Be cautious about wrapping an entire calculation in IFERROR simply to make a sheet look clean. A formula error can signal a misspelled reference, an incorrect column number, or a changed data layout. A fallback is most useful when it represents a known, expected condition and other mistakes remain discoverable.

Consider XLOOKUP as an alternative

In Microsoft 365 and Excel 2021 or later, XLOOKUP can specify what to return when it finds no match, so a separate IFERROR or ISERROR wrapper is often unnecessary. With IDs in A2:A5, prices in C2:C5, and the lookup key in E2, use:

=XLOOKUP(E2,A2:A5,C2:C5,"Not found")

For P-103, this returns 8; for P-999, it returns Not found. XLOOKUP uses exact matching by default and lets you provide lookup and return ranges separately. Unlike VLOOKUP, its return range does not have to be to the right of the lookup column. This can make formulas easier to adapt when columns move, although workbooks shared with people using older Excel versions may need a compatible formula instead.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in every older Excel release. The core ISERROR, IFERROR, ISNA, and VLOOKUP approach remains useful for compatibility with older workbooks. The formulas shown use commas as argument separators; Excel installations with different regional settings may display or require semicolons.

Frequently Asked Questions

Does ISERROR with VLOOKUP return the matching value?
Not by itself. ISERROR returns TRUE or FALSE based on whether VLOOKUP produced an error. Put it inside IF to choose between the lookup result and a message, or use IFERROR to return the lookup result unless it errors.

Should I use ISERROR or IFERROR with VLOOKUP?
Use IFERROR for a concise fallback when all errors should lead to the same result. Use ISERROR inside IF when you want to make the test explicit, and consider ISNA if you want to handle only the #N/A error caused by a missing match.

Why does my formula show #N/A even with ISERROR?
ISERROR alone reports whether an error occurred; it does not remove the error from the worksheet. Wrap the test in IF or wrap the VLOOKUP in IFERROR to display a chosen alternative. Also confirm that the key actually exists and matches the table value.

Will ISERROR catch a wrong VLOOKUP column number?
Yes. If the column number is outside the selected range, VLOOKUP can return #REF!, and ISERROR returns TRUE. IFERROR also catches that error, which is why it may hide a formula mistake if used without checking the setup.

Can I use ISERROR with approximate-match VLOOKUP?
Yes. ISERROR can test the result regardless of the match mode. However, approximate matching can return an unexpected value without producing an error, so an error test does not verify that an approximate result is correct. Use FALSE when you need an exact match.

Does this formula work in Excel for Mac or Excel for the web?
The same standard functions and formula logic are available in current Excel for Mac and Excel for the web. Menu and interface details can differ, but these formulas do not require a special Windows-only setting.

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.