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

=if(isna(vlookup(...),"",vlookup(...))

or

=if(isna(vlookup(...),0,vlookup(...))
papou replied on 28-Sep-07 09:31 AM
Hello
You can avoid errors with a test in your formula using ISERROR eg:
=IF(ISERROR(VLOOKUP(....)),"",VLOOKUP(...))

HTH
Cordially
Pascal
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
example:

=if(isna(vlookup(a1,b1:c10,2,false))=true,"",vlookup(a1,b1:c10,2,false))

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
vlookup.

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.

Pete
David Biddulph replied on 28-Sep-07 09:39 AM
=IF(ISNA(VLOOKUP(yourformula)),"",VLOOKUP(yourformula))
--
David Biddulph