Tips for Excel, Word, PowerPoint and Other Applications
VLOOKUP and IF/ISERROR Combos
Why It Matters To You
As mentioned in the Introduction to VLOOKUP article, one of the pitfalls of VLOOKUP occurs happens when there isn't an exact match. If you're just looking something up and returning a value, that might not be a big problem, but if you're planning on doing anything with those returned values, then an error message may screw up your calculations. Luckily, there are easy ways to clean VLOOKUPs and get rid of those error messages ... using an IF/ISERROR combo.
How To ...
This is a simple, but very useful tip. A lot of people use VLOOKUP, but what happens when you try to LOOKUP an exact match that doesn't exist? You get an #N/A error. If you're just doing this for information, it's not that big of a deal, but if you're trying to perform math functions on the LOOKUP values, the errors will cause your math to error too. Before you dive into this article, please familiarize yourself with the Introduction to VLOOKUP and Excel Error Messages articles.
The following examples refer to the sample file, vlookup_iserror.xls, which has the source table and examples of the two approaches taken below.
Source Table
Here's an easy example. I want to look up salary by employee name and calculate the average. Maybe I'm planning on staffing a project and want to know what my average salary will be for the team. I dutifully compile a list of candidates and their salaries from HR and start planning my team. My data table looks like this:
Example 1: VLOOKUP without IF/ISERROR
In Example 1, I select two employees and try to calculate average salary using a VLOOKUP to match a name to a salary figure. I calculate the average of the two values except that I'm using a normal VLOOKUP, which returns a #N/A because Bob doesn't exist in my source list. I forgot he retired a year ago. As a result, my AVERAGE calculation also returns an error. So what do we do now?
In the sample file, try entering any names in the blue-shaded cells B13 or B14.
The formula in cell C13 is VLOOKUP(B13,$B$6:$C$9,2,FALSE)
Example 2: VLOOKUP with ISERROR
One answer is to use an ISERROR statement. ISERROR simply tests an argument to see if it results in an error or not. If so, it returns TRUE, if not, it returns FALSE. Here's what the results of an ISERROR test on the VLOOKUP (C13) above returns. Try it out with a couple of names.
TRUE
The forumla in cell B20 is ISERROR(VLOOKUP(B17,$B$10:$C$13,2,FALSE))
OK, great, the formula tells me I have an error, but I already knew that. What now?
Example 3: VLOOKUP with IF/ISERROR
Now that you know the ISERROR function returns a binary value, TRUE OR FALSE, you can simply insert it into a IF logic statement that reads, "If the LOOKUP returns an error, show a blank, otherwise, show the LOOKUP value". This is what we do with Example 3. What this means practically, is that any error values result in a blank cell, which allows the AVERAGE formula to keep working.
IF(ISERROR(VLOOKUP(B18,$B$5:$C$8,2,FALSE)), "", VLOOKUP(B18,$B$5:$C$8,2,FALSE))
Here's what the final formula looks like. One trick I use to keep everything straight, it write my VLOOKUP first, to make sure that part is working properly, then I construct my IF/THAN using ISERROR around it.
Notes
| Last updated | 9/3/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | |
| Related Tips | Many - to be updated later |