Add to formula to put a zero in cell

[Alberta Rose]

Hi. I have this formula =VLOOKUP($A418,'Dec 07'!$F$13:$N$2000,5,FALSE). At
the moment if there is no amount in the lookup, a #N/A is returned to my
summary sheet. Then I have to go in and delete all the #N/A's before some of
the summing will work. Is there something I can add to this formula so that
a zero is populated if nothing is found?

Thanks...Laurie

 

[Dave Peterson]

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

Or if you're using xl2007:
=iferror(vlookup(...),0)

 

[Alberta Rose]

why is there two vlookups?

=if(isna(VLOOKUP($A418,'Dec 07'!$F$13:$N$2000,5,FALSE))

where in this string would i put the zero?

 

[Gord Dibben]

One vlookup is the if part, the other is the then part

=IF(ISNA(VLOOKUP($A418,'Dec
07'!$F$13:$N$2000,5,FALSE)),"",VLOOKUP($A418,'Dec
07'!$F$13:$N$2000,5,FALSE))

This formula return a blank-looking cell.

If you want a zero simply change the "" to 0


Gord Dibben MS Excel MVP

 

[Alberta Rose]

Never mind, I found the reason in an earlier post. Thanks )