Why You Should Be Cautious About Using Excel’s IFERROR Formula

by Matthew Kuo on November 8, 2015

in Error Checking, Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the


The IFERROR formula was designed to solve a common aesthetic problem that most of us have encountered when using Excel – when we know that there are errors in our data, but we’d prefer not to see Excel’s standard error message notation.  Error messages usually consist of all caps lettering that is preceded with the “#” symbol, and some of them have an exclamation point or question mark at the end.  Visually, Excel’s error messaging is intended to be “jarring” because Excel’s developers want you to alert you that something might be wrong with your file.  But if the error is expected or benign, such a loud visual indicator may not be necessary.

Enter the IFERROR formula.  IFERROR has become very popular because it allows you to mask Excel’s error message with a blank space, or a custom message of your own, such as “Not Found“.  For example, if I were to run a VLOOKUP formula on several numbers, and I knew one of the values was not going to be found within my data set, there are situations where I’d much rather see the phrase “Not Found” than the “#N/A” message.  This is especially true if I need to present this output in a more formal medium, such as a PowerPoint deck.

In the example below, imagine I was pulling rates by city from a larger data set, but I knew that Detroit and Mexico City would not be found.  The table on the right shows how you can improve the visual design of your table by using IFERROR to get rid of Excel’s “#N/A” error message.


While this is clearly a great use case for IFERROR, the key problem with using the formula is this:

IFERROR masks ALL possible Excel error types, even the ones you were not originally intending to mask

The IFERROR Formula & Syntax

Let’s start by reviewing the IFERROR formula and its syntax:

= IFERROR ( value, value_if_error )

The syntax itself is fairly simple.  Where it asks you for the value, put in your original intended formula, and where it asks for value_if_error, input the value you want to show if that original formula returns an error.

In the example above, we were using a simple VLOOKUP to pull a value from a table.  To add IFERROR to that formula, all I need to do is add the IFERROR formula prompt and indicate the value I want to show for the error condition, which is in this case “Not Found“.


The Problem with IFERROR

IFERROR becomes problematic when you encounter an error condition in your data set that you were not expecting.  We’ll continue from the previous example, but this time show the source data table that the VLOOKUP was pulling from.  In the image below, we have the same IFERROR setup to lookup values from one table to another.


You can see that Seattle and Los Angeles are pulling correctly because they both appear in the source table.

Detroit and Mexico City are both also calculating correctly, because they do not appear in the source and therefore are indicated with “Not Found“.

The problem lies with San Francisco, which shows “Not Found” but is actually there in the source table; the value just happens to have a #DIV/0! error in it.  Because IFERROR masks all error types, it ends up implying that San Francisco is not in the source table at all.

This IFERROR issue can cause huge issues, as it is not limited to just masking #DIV/0 errors.  The errors for #REF!, #NAME?, #VALUE! (just to name a few), all of which are worth your attention, would all also be masked by the IFERROR formula.  For any data sensitive situation, I’d rather have these error messages pop-up and tell me that I need to fix my data set, rather than show a table that is aesthetically pleasing, but fundamentally incorrect.

Using IF ISNA to Mask #N/A Errors

The key way to bypass the IFERROR issue, particularly for lookup formulas, is to use the IF ISNA combination formula.  The syntax is fairly simple and it was the method people generally used before the IFERROR formula was available.

= IF ( ISNA ( original formula ), value_if_error, original formula )

If we were to apply the IF ISNA approach to the same formula in the prior examples, it would look like this:


And you can see, the formula is now pulling the correct value for San Francisco, indicating that it is in our source data table, but has a divide by zero error.


The key benefit of using IF ISNA is that it looks specifically for the #N/A error in Excel.  If and only if that error occurs, then Excel will display your error condition.

However, you’ll also notice that, because you have to input your original formula twice, the formula for IF ISNA is much longer and therefore takes longer to input.  This is likely one of the primary selling point for IFERROR, in that, it takes much less time to input the formula because you don’t have to write your original formula twice.


IFERROR is an efficient way to streamline the error checking process, especially if you’re working on a data set that:

  • You are familiar with and understand all of the potential errors within it
  • The errors you do expect are relatively benign
  • Your main goal is to get a quick, clean initial summary before diving in

However, using IFERROR can cause problems down the road if you have a data set that:

  • Is too large for you to fully understand all the possible errors within it
  • Will evolve over time, potentially incorporating new unexpected errors
  • Your final output requires a high level of precision, void of any mistakes

Overall, my opinion is to avoid using IFERROR whenever possible.  It’s better to be direct about the different types of errors you are expecting.  For example, specifically denote what you want the cell to show if you encounter a divide by zero error (If denominator = 0) or not available error (IF ISNA).  This way you don’t accidentally mask an error that you weren’t expecting.

While IFERROR is definitely efficient and also very intuitive, it’s not always the best formula to use to mask errors because it can clearly cause problems down the line.

{ 3 comments… read them below or add one }

November 29, 2015 at 6:37 pm

Hi Matthew,

Great article about IFERROR. Informative with great examples and screen shots to demonstrate.

Kevin Lehrbass


June 5, 2016 at 4:03 pm

Thank you for pointing this problem out. There could be errors that we want to trap which are not errors in the data.
The syntax of IFERROR (, ) is replacing
If (ISERROR () = true,, )
because the latter runs the same twice.
The syntax that you suggest means running the same formula twice. This could be a very expensive operation. In order to avoid the situation you point out for, I would use an extra column to verify the values, rather than running the same formula twice.


December 6, 2016 at 4:08 pm

Great article.
I would suggest that if we are going to use IFERROR, we need to think about the kind of error we were going to trap and use that.

So in your example with the VLOOKUP, we can say IF(COUNTIF(B12:B16,B5)=0,”Not Found”,VLOOKUP(b5,B12:c16,2,FALSE)).

This will preserve the formatting, catch the error that we want to catch, but pass through the DIV/0 error.


Leave a Comment

{ 1 trackback }

  • November 29, 2015

Previous post:

Next post:

Related pages

adding axis labels in excelexamples for vlookup in excelhow to delete multiple entries in excelhlookup sampleuniversal studios tickets uclaexcel check for blank cellvlookup excel not workinghow to remove the duplicates in excelexcel vba range formulahow to calculate cpk in excelucla parking structure 32excel macro builderquotations in excelwhy do we use vlookupdelete duplicates excelhow to make a column graph in excelstandard curve in excelnested ifs excelvlookup example downloadhow to delete multiple cells in excelamazing excel chartsexcel making graphsvba excel templatesvlookup exactexcel chart templatesexcel chart color schemeexcel 2010 remove duplicate rowsnormal distribution graph in excellogical formulas in excel 2007 with exampleswriting if statements in excelucla webmailmr excel powerpivotvlookup hlookup pivot tableucla bruin storefinding maximum revenuevlookup truerandom name genratorvlookup with dateslookup formula in excelexcel vloopuphlookup excel examplesumif across multiple sheetsexcel remove duplicates from two columnslookup value in excelind3x hrshortcut for deleting rows in excelvlookup excel trainingtick mark symbol in pptzs associates india careersrandom name picker for contestexcel generate random passwordexcel homeschoolexcel bar widthhlookup ifexcel hlookupwhat are nested if statementsbetter than vlookupmacro in excel exampleexcel weekly calendar templatevlookup function excel 2013how to create an excel macronested if functions excel 2010excel vba syntax guidecontextures excelvlookup exactexcel project calendar templateexcel paste special values shortcutgreater than or equal to excel formulahow to pick random numbers in excelfree excel formulas with examples in excel sheetnormal distribution graph on excelhow to create a stacked bar graph in excelvlookup formula to compare two columnsmultiple if excel formulaxcel shortcutsms excel countifexcel remove duplicates from multiple columns