Overview

What Is The IFERROR Excel Function?

The IFERROR function was created by Microsoft in Excel 2007 and therefore does not exist in the 2003 version of Excel.  Its purpose is to prevent a formula from resulting in an error (#N/A, #DIV/0, etc…). 

Incorporating this function can be helpful in preventing formulas dependent on other formulas (such as a subtotal) to error out. 

The IFERROR function can also be used to make your spreadsheet more visually appealing by ridding your cells of those nasty-looking error values.  Caution must be used though, because adding this function to a formula will make it much harder to notice possible errors that could arise from different values in your cells.

Back In The ‘Ole Days

Prior to the creation of the IFERROR function, you may have had to use a formula that looked like this to rid your cells of #DIV/0 errors. 

=IF(ISERROR(A1/B1),0,A1/B1)

As you probably can imagine, if a gigantic formula required the handling of errors in various parts, adding extra IF functions was never a fun task.  Luckily, most of us can now shorten our formulas enormously.  By using an IFERROR function we can shorten our formula to state:

=IFERROR(A1/B1,0)

 That’s a length reduction of almost 40%!  Using IFERROR is also much easier to read and comprehend.  There are just too many vital benefits not to be using this function!

Real World Uses

Now that you’ve gone through what IFERROR does, you need to learn how you can put it to use.  Let’s skip all the hypothetical stuff and go straight into practical ways you can start using this function TODAY!

More Descriptive Error Messages

Instead of showing those hideous default error messages, you can use IFERROR to output an error message that is a little more intuitive.  This could be as simple as choosing to output “Not Found” or as complicated as outputting a set of directions to troubleshoot your data.

Cleanup Future Month’s Data

I try to always set up my yearly Excel workbooks for all 12 months.  This means carrying formulas out to the end of the year and forgetting about them.  The problem I see a lot (this is definitely a personal pet peeve of mine) is when formulas error out with blank data, they spit out all sorts of ugliness onto your spreadsheet.  If you take the time to process the picture above, you will notice that your eyes are first drawn to the #DIV/0! errors instead of the data.  For me, this is unbearable!  Luckily we can throw an IFERROR function around our formulas and act as if those errors never existed!

Guru Tip:  Instead of manually typing out IFERRORs around each of my formulas, I have a personal macro that will automatically do it for me!  Check out Wrapping an IFERROR Function Around Your Formulas.

Bulletproof Subtotals

This is a must for any situation where you are trying to subtotal data that relies on dividing.  When you have data that could hit zero and that data is the divisor (an example could be a specific product’s volume for a given month), there may be instances where your formula tries to divide by zero.

In case you didn’t know, it is mathematically impossible to divide by zero!

Anyways, formulas such as SUM and AVERAGE really don’t like it when you try to feed them errors.  So, in order to keep them happy you can add an IFERROR formula to prevent any errors from getting to them.

Perform Multiple Table Lookups

Have you ever had instances when you needed to perform a VLOOKUP function across multiple tabs or sets of data?  With nested IFERROR functions you can create this functionality very easily!  Since a VLOOKUP outputs an #N/A error when it cannot find an exact match, you can keep nesting IFERROR functions to look at however many datasets you need until a match is found.  Here’s an example to show you how you can write a single formula to perform HLOOKUPs across two sets of data (as shown above):

=IFERROR(HLOOKUP(M4,$B$4:$E$7,2,0),HLOOKUP(M4,$B$14:$E$17,2,0))

Why Do You IFERROR?

Now that I have gone through some scenarios that I typically use IFERROR with, I want to hear why you use the function.  Drop me a note in the comments section below!