IFERROR practice in Google Sheets
IFERROR returns its first argument, or a value of your choice if the first argument is an error. In Google Sheets it works as in Excel and is most often wrapped around a lookup.
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
Questions
Can I leave out the second argument of IFERROR in Google Sheets?
Yes. Google Sheets then shows an empty cell instead of the error. Writing "" as the second argument gives the same result and works in Excel too.
What is the difference between IFERROR and IFNA in Google Sheets?
IFERROR catches every error, while IFNA catches only #N/A. With =IFNA(VLOOKUP(D2,A2:B6,2,FALSE),"Not listed"), a missing code shows Not listed, but a #REF! or #VALUE! still shows, so you notice real mistakes. IFNA takes the same two arguments in Google Sheets as in Excel.