The IFERROR function in Excel
IFERROR runs a formula and, if it produces any error, returns a fallback you choose instead. It's the standard way to keep reports clean when a lookup can't find a match or a division hits an empty cell. Use it on purpose, for errors you expect, so real problems don't get hidden.
Syntax
=IFERROR(value, value_if_error)
| value | The formula to try, like B2/C2 or a VLOOKUP. |
|---|---|
| value_if_error | What to return if value is an error: 0, "" for a blank, or a message like "Not found". |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In D2, calculate the cost per lead (Spend ÷ Leads). Some campaigns have no leads yet, so use IFERROR to show 0 instead of an error.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Cost per lead | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: Put the division inside IFERROR, then give the fallback value as the second argument.
Examples
=IFERROR(B3/C3,0)→ 0South has no orders, so revenue divided by orders would be #DIV/0!. IFERROR returns 0 instead.
=IFERROR(B3/C3,"No orders")→ "No orders"Same calculation, but the fallback is a message that explains why there's no number.
=IFERROR(VLOOKUP("Central",A2:C5,2,FALSE),"Region not found")→ "Region not found"Central isn't in the table, so the lookup's #N/A is replaced with friendly text.
Example data
| 1 | Region | Revenue | Orders |
|---|---|---|---|
| 2 | North | 48200 | 412 |
| 3 | South | 31750 | 0 |
| 4 | East | 52900 | 455 |
| 5 | West | 27400 | 238 |
Common mistakes
Hiding mistakes you need to see
IFERROR catches every error, including a typo'd range or a broken reference. Build and test the formula first, then add IFERROR only for the errors you expect.
Text fallback in a numbers column
Returning
"n/a"makes later math like=D2*1.2fail. If other formulas use the result, return0or""and label the column clearly.Using IFERROR when IFNA fits better
For lookups, IFNA only replaces #N/A ("not found") and still shows other errors, which makes problems easier to spot.
FAQ
What's the difference between IFERROR and IFNA?+
IFERROR catches all error types. IFNA catches only #N/A, the "no match" error from lookups, so it's safer for VLOOKUP and XLOOKUP.
How do I return a blank instead of an error?+
Use empty quotes as the fallback: =IFERROR(B2/C2,"").
How do I use IFERROR with VLOOKUP?+
Wrap the whole lookup: =IFERROR(VLOOKUP(F2,A2:D9,3,FALSE),"Not found"). If the ID is missing, you see the message instead of #N/A.
Lesson
Master IFERROR step by step: IFERROR
Replace ugly errors like #DIV/0! with a clean fallback value using IFERROR, and learn when hiding errors backfires.