Vlookup with no #N/A?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
You have to put the Vlookup in an if statement

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

or

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

HTH
Cordially
Pascal
 
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!
 
In general terms, you need to do this:

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

you can replace "" with 0 if you prefer.

Hope this helps.

Pete
 
Back
Top