IFERROR Generator
Show a friendly value instead of #N/A, #DIV/0! or any other error a formula produces.
IFERROR
Show a friendly value instead of #N/A, #DIV/0! or any other error a formula produces.
The expression that may fail. Paste it without its leading equals sign.
Shown whenever the formula returns any error. Text needs quotes; use "" for a blank-looking cell or 0 for a number.
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 3, FALSE), "Not found")Worked Example
Look up a product price and show "Not found" instead of #N/A when the code is missing from the table.
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 3, FALSE), "Not found")Returns: Returns the value from the third column of D:F when A2 matches; returns the text "Not found" when the lookup fails for any reason.
Checks Before You Paste
- •IFERROR swallows every error type - #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!, plus newer ones such as #SPILL! - so a mistyped function name or a deleted column looks exactly like "no match found". Always confirm the inner formula works on a row you know should succeed before you wrap it.
- •IFERROR(...,"") does not create an empty cell: it returns a zero-length text string. ISBLANK stays FALSE, COUNTA counts it, and a line chart plots it as zero rather than leaving a gap. If a chart should skip those points, return NA() instead of "".
- •Excel requires both arguments - =IFERROR(A1/B1) is rejected as you type it. Google Sheets makes the second argument optional and defaults to blank, so a one-argument IFERROR written in Sheets is not valid Excel syntax and will not survive the trip into an .xlsx workbook. Type the second argument out if the file will ever be opened in Excel.