Excel - Vlookup with no #N/A?

Asked By dj47979 on 28-Sep-07 09:14 AM
(Excel 2003)

Is there a way to fix a vlookup so when it doesn't find a value it just
leaves the cell blank, instead of #N/A? It makes it hard for me to do sum and
other things to the results. IF not blank to replace it with zero?

Joe replied on 28-Sep-07 09:27 AM
You have to put the Vlookup in an if statement



papou replied on 28-Sep-07 09:31 AM
You can avoid errors with a test in your formula using ISERROR eg:

pablobellissim replied on 28-Sep-07 09:32 AM
I'm not sure if there is an easier way but I use an 'IF' and 'ISNA'. for


Basically, it says that if the vlookup returns #N/A then the true part of
the IF statement returns "" (blank) alternatively it returns the value of the

I'm not doing very well posting advice on here but I hope this helps anyway!
Pete_UK replied on 28-Sep-07 09:37 AM
In general terms, you need to do this:

=IF(ISNA(vlookup( ... )),"",vlookup( ... ))

you can replace "" with 0 if you prefer.

Hope this helps.

David Biddulph replied on 28-Sep-07 09:39 AM
David Biddulph