Vlookup with no #N/A?
 

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

Vlookup with no #N/A?
 

You have to put the Vlookup in an if statement

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

or

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

"dj479794" wrote:

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

Vlookup with no #N/A?
 

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

HTH
Cordially
Pascal

"dj479794" a écrit dans le message de
news: ...
(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?

Vlookup with no #N/A?
 

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,"",vlook up(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!

"dj479794" wrote:

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

Vlookup with no #N/A?
 

In general terms, you need to do this:

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

you can replace "" with 0 if you prefer.

Hope this helps.

Pete

On Sep 28, 2:14 pm, dj479794
wrote:
(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?

Vlookup with no #N/A?
 

=IF(ISNA(VLOOKUP(yourformula)),"",VLOOKUP(yourform ula))
--
David Biddulph

"dj479794" wrote in message
...
(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?