If you don’t want to see that nasty #N/A when doing lookups in Microsoft Excel, you can use the ISNA function in an if statement.
(From http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446 )
So, our criteria is IF the VLOOKUP returns #N/A, give me an empty cell, but if it doesn’t, give me the result of the VLOOKUP.
Let’s try it. First we need to think about each of the three formulas and what they must include. We already have our VLOOKUP formula and we already know what an IF function must include, so the only one we need to learn is the ISNA function. All the ISNA function does is return a value of true or false. True if the cell has a #N/A error and false if it doesn’t. So, since an IF statement wants to know if something is true or false, these two functions work very well together.
The thing you need to understand is that when you nest formulas within formulas, Excel performs the operation dictated by the deepest nested formula first, then works its way outward. So, enter the following formula into cell D2, then enter an account number in cell C2 that does not exist on your Accounts sheet and you will see that the #N/A error does not show.
=IF(ISNA(VLOOKUP(C2,Customers,2,FALSE)),””,(VLOOKUP(C2,Customers,2,FALSE)))
