Working with errors in Excel using IFERROR and ISERROR functions (example with screenshots and video tutorial)
We have previously seen the IF function and how it worked. Now we will talk about how to work with errors using IFERROR and ISERROR.
Let’s start with IFERROR. IFERROR is actually an extension to the IF function. It tells Excel what to do with other formulas when it can’t find the answer or when it returns an error.
Looking at the same example we have been using in the VLOOKUP and IF functions tutorials, we would like to see how to handle a VLOOKUP function when a city is not found (i.e. check the VLOOKUP example to understand the context).
YOU CAN DOWNLOAD THE EXAMPLE WORKSHEET FROM HERE
In this case we will use IFERROR with VLOOKUP to give a custom message when Excel can’t find what it’s looking for. We see below that Oxford can’t be found in the lookup table. So instead of Excel returning an error #N/A, we would like it to return “Not Found”.
To do this, we go to the first formula in the formula bar, and just before the VLOOKUP function just type IFERROR and open a bracket.
The first argument of the IFERROR is the value. This is the value that we want Excel to return. In this case it’s the country from the lookup table so that's why we kept the VLOOKUP function untouched.
The second argument is the value_if_error. This is what we want Excel to return when there is an error (i.e. when the city we are looking for is not found). In this case we would like to return “Not Found”, so we type Not Found between speech marks “” (since it's text), close the bracket, hit Enter, and then drag the formula.
We will see that Oxford now shows Not Found instead of showing an error.
Now, what if I have more than one lookup table, and I wanted Excel to look up another table when the value I’m looking for is not found in the first table.
Let’s make another table with only the city Oxford in it, and we will use this as our second lookup table (it's a relatively small table that we make for the sake of the example).
Now to make this work, we go to our first IFERROR formula, and instead of typing “Not Found”, we will type another VLOOKUP function to lookup the other table with Oxford in it.
By doing this, I’m asking Excel to lookup Oxford in the first table (this is the first VLOOKUP), and if it’s not found (i.e. it returned an error), look it up in the second table. And as shown below, it works perfectly as desired.
Now to make it a bit harder, let’s consider another scenario where we have another city that is not included in both lookup tables (note the city Chester below).
So now we would like Excel to return a custom message instead of the error #N/A.
This is where we use what's called "nested formulas". Nested formulas are basically a formula inside a formula. Meaning the we will use an IFERROR inside an IFERROR. I know this might sound confusing, but it’s actually not. Just follow along.
The logic that we want Excel to follow is the following:
So let’s see how the above logic can be applied into our formula. Our initial formula satisfied only 2 conditions from the above logic (conditions 1 and 2):
Now to update this formula to satisfy the 3 parts of our logic, we will need to include another IFERROR function before the second VLOOKUP. So the final formula will look like this:
Once we apply it, it will work exactly as intended:
So this is how you use IFERROR to clean up your data when there are errors in your formulas.
Now let’s see how ISERROR works in dealing with errors in Excel.
What ISERROR does is that it returns a True or False of whether or not there is an error.
So what we will do is that we will add a new column called “In Table?” and check if whether or not the cities are found in the lookup table.
So we go to the formula bar and type = ISERROR and then our VLOOKUP formula.
Note that there is only 1 argument in the ISERROR function and that’s because it returns either True or False.
So the formula returned FALSE for all the cities except for Oxford and Chester since they are not found in the lookup table and their VLOOKUPs returned an error (thus, it's TRUE, it has an error).
Now let’s clean this a little bit further. Let’s wrap an IF function around this formula to return “Found” if the city was found in the lookup table, and “Not Found” if it wasn’t there.
So the formula should look like this:
What happened here is that Excel did the VLOOKUP and checked, was the result an error? If it was an error, then “Not Found” is returned. Otherwise, “Found” is returned.
So this is another typical example of nested formulas. We used IF, ISERROR and VLOOKUP functions all in one formula. We could have done each separately in separate columns and separate formulas, but with this nested formula capability, we were able to save time and space by compiling all of them together in just one formula. It also makes your worksheet much tidier.
Now go ahead and try these handy functions with your data. Feel free to post your questions in the comments section below.