I am using the following conditional formula
=IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE), 0)

So if the cell F2 is populated it will complete a vlookup on a second tab
within the worksheet.

Issue: The list on the second tab does not contain all the items in the
list referenced by $B6, so I get a #N/A return.

I want to remove the #N/A as a returned value because I am summing the
column of values returned by the Vlookup. With a value of #N/A, the column
sums to a #N/A.

Can I nest an iserror or an isan statement in the formula to remove the #N/A
and return a value of 0?

=IF(ISNA(VLOOKUP([your vlookup critieria]),"",IF(F$2<0,VLOOKUP([your
vlookup]),0))

Dave
--
Brevity is the soul of wit.


"Dale" wrote:

I am using the following conditional formula
=IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE), 0)

So if the cell F2 is populated it will complete a vlookup on a second tab
within the worksheet.

Issue: The list on the second tab does not contain all the items in the
list referenced by $B6, so I get a #N/A return.

I want to remove the #N/A as a returned value because I am summing the
column of values returned by the Vlookup. With a value of #N/A, the column
sums to a #N/A.

Can I nest an iserror or an isan statement in the formula to remove the #N/A
and return a value of 0?

=IF(ISNA(VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE))," 0",VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE))
in other words =IF(ISNA('your VLOOKUP)),"0",'your VLOOKUP))
Regards,
Alan.
"Dale" wrote in message
...
I am using the following conditional formula
=IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE), 0)

So if the cell F2 is populated it will complete a vlookup on a second tab
within the worksheet.

Issue: The list on the second tab does not contain all the items in the
list referenced by $B6, so I get a #N/A return.

I want to remove the #N/A as a returned value because I am summing the
column of values returned by the Vlookup. With a value of #N/A, the
column
sums to a #N/A.

Can I nest an iserror or an isan statement in the formula to remove the
#N/A
and return a value of 0?

=IF(ISNA(IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1200,19 ,FALSE),0)),"",IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1 200,19,FALSE),0))

Returns "" if #N/A.

Change to 0 if you wish


Gord Dibben MS Excel MVP

On Fri, 5 Jan 2007 13:48:01 -0800, Dale wrote:

I am using the following conditional formula
=IF(F$2<0,VLOOKUP($B6,Jan!$E$2:$W$1200,19,FALSE) ,0)

So if the cell F2 is populated it will complete a vlookup on a second tab
within the worksheet.

Issue: The list on the second tab does not contain all the items in the
list referenced by $B6, so I get a #N/A return.

I want to remove the #N/A as a returned value because I am summing the
column of values returned by the Vlookup. With a value of #N/A, the column
sums to a #N/A.

Can I nest an iserror or an isan statement in the formula to remove the #N/A
and return a value of 0?