IFERROR practice exercises
Catch errors like #N/A and #DIV/0! and show something friendlier with IFERROR.
How IFERROR works
IFERROR tries a formula and shows something else if it returns an error. It takes two arguments: the formula to try, and what to show if it fails. When there is no error, you simply get the formula's normal result. It works the same in Excel and Google Sheets and catches every error type, including #N/A, #DIV/0!, #VALUE! and #REF!.
=IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Unknown") shows Unknown instead of #N/A when the code in D2 is not in the table. =IFERROR(B2/C2,0) shows 0 instead of #DIV/0! when C2 is zero. The second argument can be text in quotes, a number, a cell or another formula.
IFERROR hides every error, including the ones that point to a real mistake, such as a misspelled function name or a deleted column. When you only expect a value to be missing, IFNA is safer because it catches #N/A and lets everything else show. Wrap only the part that can fail. The exercises below cover hiding #N/A, avoiding #DIV/0! and catching only not found with IFNA.
- 1Hide #N/A with IFERRORIS is not in the table, so a plain VLOOKUP shows #N/A. In E2, wrap VLOOKUP(D2,A2:B6,2,FALSE) in IFERROR to show "Unknown" instead.
- 2Avoid #DIV/0! with IFERRORPremiumThe lamp has not sold yet, so B2/C2 gives #DIV/0!. In D2, wrap B2/C2 in IFERROR to show 0 instead.
- 3Catch only "not found" with IFNAPremiumE-109 is a new hire who isn't in the list yet. In E2, look up the department for D2 with VLOOKUP and wrap it in IFNA to show "Not listed" instead of #N/A.
Functions in this topic
Read the guide
Questions
How do I show a blank cell instead of an error in Excel?
Use two quotation marks with nothing between them as the second argument: =IFERROR(B2/C2,"") shows an empty-looking cell when the division fails. Keep in mind that the cell still holds an empty text value, so a later formula that expects a number may show an error of its own.
How do I use IFERROR with two lookup tables?
Put a second lookup inside the first IFERROR: =IFERROR(VLOOKUP(A2,Table1,2,FALSE),VLOOKUP(A2,Table2,2,FALSE)). Excel tries the first table, and only if that fails does it try the second. Add a third IFERROR around the second lookup if you want a final message like "Not found".
Is IFERROR the same as IF(ISERROR(...))?
The result is the same, but IFERROR is shorter because it evaluates the formula only once. Compare =IF(ISERROR(B2/C2),0,B2/C2) with =IFERROR(B2/C2,0). IFERROR has been in Excel since Excel 2007, so you only need the longer version for files that must open in Excel 2003.