IFERROR
The IFERROR formula in Excel returns a value you specify if a formula results in an error, if there is no error it returns the value of the original formula.
The IFERROR function is relatively new to Excel (I believe it started in Excel 2007). For a similar workaround to some VLOOKUP formula error results, I talk later in this post about “IF ISNA”.
To demonstrate this and explain further I will be using the formulas listed below. If you are unsure of any of these, click on the link in the formula name to see a post about them.
You should also be familiar with this post regarding brackets and commas. I will repeatedly talk about the importance of paying attention to these to break down formulas and make them easier to read.
IFERROR EXAMPLE
I have a file with 3 tables, for this example the 3 tables are on the same sheet beside each other. In the real world the tables will probably be in other sheets or indeed, other files.
- The first table (columns A to C) list the personal details of our players: First Name, Surname, Age.
- The second table (columns E to F) is a record of our players’ scores in a game.
- The third table (H-L) will be the table we will populate using formula. The first column of this table lists the first names of all our players.
Our formulas will include:
- VLOOKUP TO get the SURNAME from the first table
- SUMIF to get the total score for each player from the second table
- COUNTIF to count the number of games each player played from the second table
- The Average Score will be the total score divided by the number of games played.
Our file with the above formulas now looks like this:
Our formula has resulted in two types of error – “#N/A” and “#DIV/O!”.
At this point, don’t worry about what is causing the errors, I have put in some deliberate errors for demonstration. Before we look at the cause of the errors, we will add in the IFERROR function.
The syntax for IFERROR is =IFERROR(value, value_if_error).
- value – this is the formula that we want to validate
- value_if_error – this is what we want our function to say if the original formula (value) results in an error.
Our first VLOOKUP formula is =VLOOKUP(H3,$A$3:$B$15,2,0). This formula is our first ‘value’.
If our VLOOKUP returns an error, we want our function to say “Not found”. This is our ‘value_if_error’.
Our first full IFERROR formula is =IFERROR(VLOOKUP(H3,$A$3:$B$15,2,0),”Not Found”)
The formula is starting to grow a little, but don’t worry – watch the brackets and commas to break it down.
In your IFERROR function, you know the first part of the syntax is the VLOOKUP formula which gives you your value. You know what makes up the syntax of the VLOOKUP formula after each comma and within the overall brackets. The VLOOKUP here is no different to a VLOOKUP on it’s own.
After the VLOOKUP there is a comma and that brings you on to the value_if_error part of your syntax, where you state “Not Found”.
So, in short – if the VLOOKUP returns an error then we want to see “Not Found”.
In the above image, you can see that Allan’s surname is “Not Found” which is the value_if_error we stated in our formula. In the last image the VLOOKUP returned “#N/A”. All other names have an IFERROR function in column I, but because their VLOOKUP does not return an error, we get the value of the VLOOKUP instead.
IFERROR Tip!
Personally, I write my initial formula first (VLOOKUP, SUMIF, COUNTIF, etc) and then I write the IFERROR function around the original formula. This leaves you with just one formula to think about at a time!!
Then I copy the full IFERROR function down across the rows.
More IFERROR
For the remaining 3 columns (J-L) I will add in the IFERROR function along with the existing formula (SUMIF, COUNTIF, division).
This time though, if our formulas return an error I want to see zero (“0”), so the value_if_error is 0.
Our SUMIF formula in Cell J3 becomes =IFERROR(SUMIF($E$3:$E$22,H3,$F$3:$F$22),0)
Again, this is a SUMIF formula, just contained within an IFERROR function. The syntax for the SUMIF has not changed and neither has the syntax for the IFERROR formula.
I’ve said it before and I’ll say it again….. pay attention to the brackets and commas and they will help you breakdown formulas easier!
IFERROR – Leave blank
If your formula returns an error, you might not want any value returned. For example in the above table instead of having zero score for Ivan, you might want to just leave the cell blank. To do this, your value_if_error is two double apostrophes (“”).
Warning About Using IFERROR
Even though IFERROR is really useful, it can also cause as much pain as it relieves!
In our example I have added IFERROR to other formulas and this has resulted in a table free of messy #N/A and #DIV/0!.
However, it has also hidden an error that I created. Our first table starts with the name “Alan”, but when creating the 3rd table I used the name “Allan”. The VLOOKUP is trying to find “Allan” in the first table, where it doesn’t exist, so instead of getting “Alan”‘s surname we see the value_if_error.
In all cases, where I use the IFERROR function, I have a quick and easy way to tell if it has worked fully. I don’t design my files to give me this quick check, it just happens.
In our case here, highlighting the scores in the 2nd table gives us a total of 528,915. But highlighting the scores on the 3rd table only gives us 391,305.
Now if I correct the name to “Alan” and highlight the scores in the 3rd table I get a total of 528,915 – the same as the total of the 2nd table.
IF ISNA
IF and ISNA are two separate formulas that can be used together.
ISNA tests whether a value is #N/A and returns true or false.
I have changed Alan back to Allan in our 3rd table.
Our original VLOOKUP was VLOOKUP(H3,$A$3:$B$15,2,0).
Applying our ISNA to this gives us =ISNA(VLOOKUP(H3,$A$3:$B$15,2,0)).
For Allan, this returns “True”, that is the result of the VLOOKUP is #N/A. For the others, the ISNA returns “False” because the VLOOKUP does not result in #N/A.
The syntax for an IF statement =IF(logical_test, value_if_true, value_if_false).
Our logical test is the ISNA formula (which contains a VLOOKUP formula).
Our value_if_true is what we want to see if the ISNA is true – for this example we will use “Not Found” again.
The value_if_false is what we want to see if the ISNA is false – in other words if our VLOOKUP does not return and #N/A then we want to see the result of the VLOOKUP. Here our value_if_false is the original VLOOKUP formula.
The full IF ISNA formula is =IF(ISNA(VLOOKUP(H3,$A$3:$B$15,2,0)),”Not Found”,(VLOOKUP(H3,$A$3:$B$15,2,0)))
Does it look complicated??
Remember – pay attention to the brackets and commas and breakdown the formulas!
The VLOOKUP syntax hasn’t changed, neither has the IF statement syntax. The ISNA may be new to you, but it has a straightfoward syntax.
IF ISNA Tip
When I used to create IF ISNA formulas, I would always write the VLOOKUP formula first. Then in the formula bar, I would
- highlight the VLOOKUP formula (except for the = sign) and copy it by pressing Ctrl+C
- After the = sign, I would type “IF(ISNA(“
- Press the “End” key to jump to the end of the VLOOKKUP
- Then type “),”Not Found”,(“
- Then press “Ctrl+V” to paste the original VLOOKUP.
- Finally I would close off the formula with 2 brackets.
This may seem longwinded, but it is just a few keyboard shortcuts.
A Final Note On Brackets And Commas
Have you understood the IFERROR formula so far?
What if I take the 3 tables and save them as their own files, so one table on each file, and move our First Name list and Surname VLOOKUP to columns A and B of a file on their own.
Now, can you understand this formula ?
=IFERROR(VLOOKUP(A3,’C:\Users\JayTray\Documents\sitework\excel\formula examples\[PERSONAL DETAILS.xlsx]Sheet1′!$A:$C,2,0),”Not Found”)
It shouldn’t be too hard – at first it may look daunting, but (here we go again!) pay attention to the brackets and commas and breakdown the formula!
The IFERROR and VLOOKUP both follow the normal syntax, the range of the VLOOKUP is in another file and the table array in the formula shows the full filepath, filename, sheet name and cell range of the table.
