Vlookup with no #N/A?

[Guest]

(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?

 

[Guest]

You have to put the Vlookup in an if statement

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

or

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

 

[papou]

Hello
You can avoid errors with a test in your formula using ISERROR eg:
=IF(ISERROR(VLOOKUP(....)),"",VLOOKUP(...))

HTH
Cordially
Pascal

 

[Guest]

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]

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]

=IF(ISNA(VLOOKUP(yourformula)),"",VLOOKUP(yourformula))