Imagine that we have a table of data such as:
 Errors.JPG)
And then we do a VLOOKUP on a city that isn’t there we get an N/A error:
 Errors.JPG)
This is not much of an issue if we have only a single N/A error. However, if we have several N/A (each arising from a separate VLOOKUP) in a table then it can look rather unsightly. In addition any totals that involve these cells also result in the value N/A. In short the N/A error can propagate through the entire workbook:
 Errors.JPG)
To get around this, Excel has the function ISNA which allows us to check whether or a condition or a cell has the value N/A. It is usually used as part of an IF statement:
B1 =IF (ISNA(A1), 0,A1)
i.e if the cell A1 has the value ISNA then the cell B1 takes the value 0 otherwise it has the value in A1.
Or if we have a VLOOKUP formulae, we can check whether or not it evaluates to NA using the same approach. If does then we take the value 0 otherwise we take the value returned by the VLOOKUP value:
=IF(ISNA(VLOOKUP(B18,B6:C13,2,FALSE)),0, VLOOKUP(B18,B6:C13,2,FALSE))
And if this approach is adopted in the individual rows in the above table we have:
 Errors.JPG)
Which is much more presentable and the totals now evaluate sensibly.
September 20th, 2008 at 12:51 pm
An even better way is:
IF(COUNTIF(B6:C13,B18)=1,vlookup(B18,B6:C13,2,FALSE),”")
This avoid the VLOOKUP being run twice, if the value is actually in the lookup array.
–JP
September 20th, 2008 at 3:41 pm
Good tip on using COUNTIF(), but I think the condition should be greater than zero instead of equal to one. It depends on whether the item being in the lookup table more than once is a problem, and how to handle the problem.
Also, I’ve tried to get in the habit of only hard-coding the column number when I’m doing “quick and dirty” VLOOKUP() function. In a long-term application, it can be a problem if you start inserting columns in a table. So, instead of:
=VLOOKUP(B18,B6:C13,2,FALSE)
I’d use:
=VLOOKUP(B18,B6:C13,COLUMNS(B:C),FALSE)
Then everything automatically adjusts if you insert a new column in your table for some reason.
September 20th, 2008 at 4:55 pm
I guess it depends on your approach. My formula will return nothing if either the lookup value doesn’t exist in the array, or if it exists more than once. You might want to be alerted to that fact, and manually check your data for dupes. If you use the “>0″ approach, you won’t know.