IFERROR catches formula errors and replaces them with a message you choose. Here is everything you need — syntax, 6 real examples, common mistakes and FAQ.
IFERROR catches formula errors and replaces them with a message or value you choose. Without it, errors like #N/A, #VALUE!, or #DIV/0! appear raw in your spreadsheet — confusing to anyone reading it. IFERROR makes your spreadsheets look professional and prevents errors from breaking downstream calculations.
It is one of the most commonly used wrapper formulas — you will find it around VLOOKUP, XLOOKUP, INDEX MATCH, and division formulas in virtually every professional spreadsheet.
=IFERROR(value, value_if_error)| Argument | Description |
|---|---|
| value required | The formula to evaluate. IFERROR runs this first and checks whether it returns an error. |
| value_if_error required | What to return if value produces any error. Can be text, a number, blank (""), or another formula. |
| Error | Meaning | Common cause |
|---|---|---|
| #N/A | Value not found | VLOOKUP, XLOOKUP, MATCH can't find a match |
| #VALUE! | Wrong data type | Text where a number is expected |
| #DIV/0! | Division by zero | Dividing by an empty or zero cell |
| #REF! | Invalid reference | Deleted cells that were referenced |
| #NAME? | Formula name not recognised | Typo in a formula name |
| #NUM! | Invalid numeric value | DATEDIF start > end, SQRT of negative |
| #NULL! | Invalid intersection | Incorrect range operator |
IFERROR handles every error type in one formula. If you only want to catch #N/A specifically (and let other errors show), use IFNA instead.
The most common use — show a message instead of #N/A when a lookup fails.
=IFERROR(VLOOKUP(A2, PriceList!$A:$B, 2, 0), "Not found")Use "" to return an empty cell rather than any text — keeps the spreadsheet clean.
=IFERROR(VLOOKUP(A2, $C:$D, 2, 0), "")=IFERROR(A2/B2, 0)If B2 is zero or empty, return 0 instead of #DIV/0!. Useful for percentage calculations where the denominator might be missing.
=IFERROR(INDEX(B:B, MATCH(E2, A:A, 0)), "Not in list")The value_if_error can itself be a formula — try one calculation, fall back to another.
=IFERROR(A2/B2, AVERAGE(A:A))If division fails, return the column average instead.
Try XLOOKUP first, fall back to VLOOKUP, then to "Not found".
=IFERROR(
XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B),
IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0), "Not found")
)
| Formula | Catches | Use when |
|---|---|---|
| IFERROR | All error types | You want any error silenced |
| IFNA | #N/A only | You want lookup failures silenced but other errors visible |
For lookups, prefer IFNA over IFERROR. IFERROR hides all errors including genuine formula mistakes like wrong column numbers. IFNA only hides "not found" — so if you make a mistake in the formula itself, you'll still see the error.
If you have a typo in your VLOOKUP column number, IFERROR hides the #VALUE! error and you never find out. Build and test your inner formula first, then wrap with IFERROR once you're sure it's correct.
IFERROR treats the symptom, not the cause. If your data has duplicates, extra spaces, or inconsistent formatting causing lookup failures, fix the data. Use IFERROR as a safety net, not a permanent solution.
ExcelPro exercises use IFERROR alongside VLOOKUP, XLOOKUP and INDEX MATCH across all tracks. Free to start.
Try lookup exercises →