Logical
IFERROR in Excel:syntax, examples and errors
IFERROR returns the result of a formula and, if that formula returns an error, the value you choose. In Spanish Excel it is called SI.ERROR. Syntax: =IFERROR(value, value_if_error).
Syntax
=IFERROR(value, value_if_error)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
value | Required. The formula that may return an error. Example: B2/C2. |
value_if_error | Required. What is shown if the formula returns an error: text in quotes, a 0 or "" to leave the cell blank. Example: "No data". |
How do you use IFERROR? A step-by-step example
IFERROR swaps an error for whatever you want to show. In this example you work out the sales per unit of 4 stores. The North store sold nothing this month and Harbor has not sent its units yet.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Store | Sales | Units | Per unit |
| 2 | Central | 1840 | 46 | |
| 3 | North | 0 | 0 | |
| 4 | South | 990 | 33 | |
| 5 | Harbor | 1200 |
=B2/C2 returns 40, but =B3/C3 gives #DIV/0! because it divides by 0. With =IFERROR(B3/C3,0) the cell shows 0, and with =IFERROR(B5/C5,"No data") it shows No data.
- The first argument is the usual formula:
B3/C3. - The second is what you see if it errors:
0,""or a text. - If there is no error, IFERROR returns the normal result:
=IFERROR(B2/C2,0)still gives 40.
How do you use IFERROR with VLOOKUP?
It is the most common use. With a price list in G2:H3 (P-101 and P-103), looking up P-999 with =VLOOKUP(E3,G2:H3,2,FALSE) gives #N/A. Wrapped in IFERROR:
=IFERROR(VLOOKUP(E3,G2:H3,2,FALSE),"Not found") returns Not found, and with the code P-103 it still returns 6.8.
If you have XLOOKUP, you do not need IFERROR: its fourth argument is already the message.
IFERROR, IFNA or IF?
| IFERROR | IFNA | IF with a check | |
|---|---|---|---|
| What it catches | Any error | Only #N/A | What you check |
| Example | =IFERROR(B3/C3,0) | =IFNA(VLOOKUP(E3,G2:H3,2,FALSE),"Not found") | =IF(C3=0,0,B3/C3) |
| Versions | Excel 2007 and later | Excel 2013 and later | All |
| Risk | Hides errors you did not expect | None in lookups | Longer to write |
| When to pick it | Divisions and formulas you already know work | Lookups | When you want to make clear why it fails |
=IF(C3=0,0,B3/C3) returns the same 0 as IFERROR, but only in the case you expect: if C3 held text, you would still see the error.
How does IFERROR show up in an Excel test?
In a job interview it is usually the second half of a lookup: "Now make it say Review if the code does not exist." They expect =IFERROR(VLOOKUP(A2,$G$2:$H$50,2,FALSE),"Review").
In a skills test:
What does
=IFERROR(10/0,"Error")return? a) 0 · b) #DIV/0! · c) Error · d) 10
Answer: c. 10/0 gives #DIV/0!, so IFERROR shows the second argument.
Practice IFERROR
The English exercises are on the way. Until then, build the stores table in a blank sheet, divide each row, and wrap the formula in IFERROR once you can see which cells fail. See IF for the longer way to do the same check, and COUNTIF to count how many rows come back with an error flag.
Available in: Excel 2007 and later, on Windows, Mac and Excel for the web. Source: Microsoft: IFERROR function. Results calculated with Formulo's engine.
Common mistakes
- It hides an error you should fix
- IFERROR catches every error, not only the one you expect. If you pick the wrong column in a lookup, =IFERROR(VLOOKUP(E2,G2:H3,3,FALSE),"Not found") shows Not found for a code that does exist, instead of the #REF! that would warn you. Write the formula without IFERROR first, check that it works, and add IFERROR at the end.
- It puts 0 where a 0 misleads
- A 0 in place of #DIV/0! enters sums and averages as if it were data. If "no data" is not the same as "zero", use "" or a text such as "No data".
- The second argument is missing
- IFERROR needs both. Excel does not accept =IFERROR(B2/C2) and warns you when you press Enter.
Frequently asked questions
What is IFERROR called in Spanish?
SI.ERROR. In a Spanish Excel: =SI.ERROR(B2/C2;0).
How do I remove the #N/A from VLOOKUP?
Wrap the lookup: =IFERROR(VLOOKUP(E3,G2:H3,2,FALSE),"Not found"). First check that the #N/A is not caused by an extra space in the code.
What is the difference between IFERROR and IFNA?
IFERROR catches any error. IFNA (Excel 2013 and later) only catches #N/A, so a #REF! caused by a wrong column stays visible: =IFNA(VLOOKUP(E2,G2:H3,3,FALSE),"Not found") returns #REF!. For lookups, IFNA is safer.
How do I leave the cell blank if there is an error?
Use two quotes in a row as the second argument: =IFERROR(B3/C3,"").
Since which version does IFERROR exist?
Since Excel 2007. In earlier versions you used IF with ISERROR.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.