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

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

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



Alberta Rose wrote:

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

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?


"Dave Peterson" wrote:

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

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



Alberta Rose wrote:

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

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

On Thu, 17 Sep 2009 14:21:01 -0700, Alberta Rose
wrote:

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?


"Dave Peterson" wrote:

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

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



Alberta Rose wrote:

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

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

"Dave Peterson" wrote:

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

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



Alberta Rose wrote:

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