Excel Question: How to I get rid of "N/A" when doing a VLookup?

Question

When I do a VLookup, if there is no data to return, Excel puts the following "N/A" in the empty space. How to I get rid of "N/A" when doing a VLookup? Right now, I'm deleting it row by row.

Answer 1

Test for an error, if you get one return a blank, otherwise return your value:



=IF(ISERROR(VLOOKUP(C1,A$1:A$10,1, FALSE)),"",VLOOKUP(C1,A$1:B$10,2, FALSE))



In Excel 2007 there is a shortcut:



=IFERROR(VLOOKUP(C1,A$1:B$10,2,FALSE) ,"")



Hope that helps...

Answer 2

Doing A Vlookup

Answer 3

Assuming your formula is like this

=VLOOKUP(C1,$A$1:$A$10,2.0)



The #N/A result means: data like "data in C1" is not available in A1:A10

so you can chek it before the VLOOKUP work, using a logical test

COUNTIF($A$1:$A$10,C1) > 0



=IF( COUNTIF($A$1:$A$10,C1)>0, VLOOKUP(C1, $A$1:$A$10, 2, 0), "" )