As great as VLOOKUP is, there are some additional functions that make it more useful. One of these is the IFERROR function (ISERROR for older versions of Excel). With VLOOKUP, sometimes an error message like #N/A is actually a good thing, it is telling you that something is missing.
In the example below we have joined 2 databases to see who is owed ‘out of office expenses’. Certain employees are owed some money, but the others aren’t. However, for VLOOKUP this generates some #N/A errors and the real problem happens when you try and sum the out of office expenses. The sum formula in C14 returns a #N/A instead of the sum of the 2 employees who are owed money.
In order to correct this we need to tell Excel that IF it returns an ERROR message it should replace it with something else.
That’s where the IFERROR comes into play. The syntax of the IFERROR is
=IFERROR(Value, Value_If_Error)
The Value can be a full formula (e.g. a vlookup formula) or a link to another cell which contains the formula.
The Value_if_error is what you want Excel to do if it finds an error (if it doesn’t find an error is assumes you want to see the value- more of this later).
As shown below, if we wrap the IFERROR around the VLOOKUP, then any cell that previously showed a #N/A error will show a 0, and cells that had a result stay the same. We are now able to add up how much we owe.
This same function can be used if you want to replace the error message with a blank, some other text or number.
For a blank the IFERROR above would look as follows
=IFERROR(VLOOKUP(A13,$G$8:$K$35,5,FALSE),“”) – Notice the 2 inverted commas directly next to each other to indicate a blank. If you put a space between the inverted commas it will replace the error message with a space which is different from a blank.
For a text message the IFERROR above would look as follows
=IFERROR(VLOOKUP(A13,$G$8:$K$35,5,FALSE),”Zero Balance”) – Notice the 2 inverted commas around the words ‘Zero Balance’.
For any other number you would do the same as the 0 (i.e. no inverted commas) and just put the number you want.
ISERROR with an IF
In older versions of Excel you had to achieve the above by combining an IF and ISERROR function. You can now use IFERROR, but there is one limitation to IFERROR.
What if we just want to check who we owe money to and want the word ZERO to appear if we owe nothing and CREDITOR if there is an amount outstanding.
Because of the set-up of the IFERROR you cannot specify an alternate to the value. If there is no error it will return the value whether you like it or not.
The ISERROR function is an information function and all it returns is a TRUE or a FALSE.
So if you wrapped the ISERROR around our VLOOKUP like this
=ISERROR(VLOOKUP(A13,$G$8:$K$35,5,FALSE))
the answer would be TRUE if it is an error or FALSE if it isn’t
To take it a step further, we can now add an IF to tell it what to do if it is TRUE or FALSE.
=IF(ISERROR(VLOOKUP(A13,$G$8:$K$35,5,FALSE)=TRUE,”ZERO”,”CREDITOR”)
By using one of these functions you are better able to control what the VLOOKUP results will be and use them in other calculations.