=IFERROR(B2/C2,0) returns the result of B2/C2, or 0 when that result is an error. The first argument is the formula you want; the second is what to show instead of any error it produces.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Plain | With IFERROR |
| 2 | Pens | $120 | 80 | $1.50 | $1.50 |
| 3 | Paper | $300 | 50 | $6.00 | $6.00 |
| 4 | Ink | $90 | 0 | #DIV/0! | $0.00 |
| 5 | Tape | $45 | 30 | $1.50 | $1.50 |
| 6 | Clips | $0 | 0 | #DIV/0! | $0.00 |
Ink and Clips have 0 units, so the plain division in column D shows #DIV/0!. Column E shows $0.00 for them and the normal price for every other row. Type 15 in C4 and both columns show Ink's price.
IFERROR syntax
=IFERROR(value, value_if_error)
valueis the formula to calculate.value_if_erroris returned whenvalueis any error: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, and the newer ones such as #CALC!.- If
valueis not an error, IFERROR returns it unchanged.
The replacement can be a number (0), text ("Not found"), empty text ("") or another formula, for example a second lookup in another table: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),VLOOKUP(E2,G2:I6,3,FALSE)).
IFERROR with VLOOKUP
A lookup returns #N/A when the value is not in the table. Wrapping it in IFERROR shows a message instead:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | Kiwi | Not found | |
| 4 | Carrot | Vegetable | $0.80 | Milk | $1.10 | |
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Kiwi is not in the list, so F3 says Not found. Type Kiwi in A4 instead of Carrot and F3 finds it. With XLOOKUP you do not need IFERROR for this, because its fourth argument is the "not found" value: =XLOOKUP(E2,A2:A6,C2:C6,"Not found").
IFNA: catch only #N/A
IFNA works like IFERROR but only replaces #N/A. For lookups that is usually what you want: #N/A means "not found", which is a normal answer, while any other error means the formula itself is wrong. In this sheet the formulas ask for column 4 of a three-column table, a typo:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | IFERROR | IFNA | |
| 2 | Apple | Fruit | 1.2 | Pear | Not found | #REF! | |
| 3 | Pear | Fruit | 1.5 | ||||
| 4 | Carrot | Vegetable | 0.8 | ||||
| 5 | Bread | Bakery | 2.4 | ||||
| 6 | Milk | Dairy | 1.1 |
Pear is in the table, yet F2 says Not found: IFERROR turned the #REF! from the bad column number into the same message as a missing product. G2 lets the #REF! through, so you see that the formula is broken. Change the 4 to 3 in G2 and it returns 1.5. IFNA needs Excel 2013 or later.
Return a blank instead of an error
To show nothing, use empty text, two double quotes, as the replacement:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Growth |
| 2 | Jan | 200 | 240 | 20% |
| 3 | Feb | 0 | 150 | |
| 4 | Mar | 180 | 171 | -5% |
| 5 | Apr | 90 | ||
| 6 | May | 250 | 300 | 20% |
February and April had no sales last year, so their growth cannot be calculated and the cell stays empty. The other months show 20%, minus 5% and 20%. A cell with "" holds text: SUM and AVERAGE skip it, but =D3*2 gives #VALUE!. If later formulas do arithmetic on the column, return 0 instead.
Practice: look up with a fallback
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Look for | Stock | ||
| 2 | Apple | 40 | Kiwi | |||
| 3 | Pear | 25 | ||||
| 4 | Carrot | 60 | ||||
| 5 | Bread | 12 | ||||
| 6 | Milk | 30 |
Your turn: In F2, look up the stock of the product in E2 from A2:B6, and show "Not found" when it is not in the list.
Why hiding every error can hide mistakes
IFERROR does not fix anything; it decides what the cell shows. Before you wrap a formula in it:
- Find out why the error happens. When a blank Units cell causes #DIV/0!, the real fix may be data that someone should enter, not a zero price.
- Prefer IFNA for lookups, so that a wrong column number (#REF!), a misspelled name (#NAME?) or text in a number column (#VALUE!) still shows.
- Test the specific case for divisions.
=IF(C2=0,0,B2/C2)handles a zero divisor and nothing else; a wrong reference in B2 still shows its error. The page on #DIV/0! compares the two approaches. - Pick a replacement that cannot be mistaken for data. A 0 in a price column looks like a real price and lowers the average;
""or "Not found" does not.
Wrap the formula last, once it gives the right result on the rows that should work.
Frequently Asked Questions
How do I use IFERROR with VLOOKUP?
Wrap the lookup: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). When E2 is not in the first column, the cell shows Not found instead of #N/A. =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found") does the same and still shows other errors.
How do I make IFERROR return a blank cell?
Use empty text as the second argument: =IFERROR(B2/C2,""). The cell looks empty, but it holds text, so =D2+1 on it gives #VALUE!; SUM and AVERAGE skip it.
What is the difference between IFERROR and IFNA?
IFERROR replaces every error: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. IFNA replaces only #N/A, the "not found" of lookups, and lets every other error show, so a broken formula is not hidden.
How do I replace #N/A with 0 in Excel?
Wrap the formula in IFNA with 0 as the value: =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),0). XLOOKUP has the replacement built in as its fourth argument: =XLOOKUP(E2,A2:A6,C2:C6,0).
Which Excel versions have IFERROR and IFNA?
IFERROR exists since Excel 2007 and IFNA since Excel 2013. In older files you may see =IF(ISERROR(B2/C2),0,B2/C2), which does the same job as IFERROR but calculates the formula twice.
