Hi, i have a lookup i'm currently using; "=VLOOKUP(A3, ELMAX, 2,)" I want to
expand this formula to include an IF(ISNA aspect as when the value being
looked up doesn't occur in the lookup table i am getting #N/A!'s and want to
have them come through as zeros...any thoughts?

=IF(ISERROR(=VLOOKUP(A3, ELMAX, 2,)),"0",(=VLOOKUP(A3, ELMAX, 2,)))


"GD" wrote:

Hi, i have a lookup i'm currently using; "=VLOOKUP(A3, ELMAX, 2,)" I want to
expand this formula to include an IF(ISNA aspect as when the value being
looked up doesn't occur in the lookup table i am getting #N/A!'s and want to
have them come through as zeros...any thoughts?

Cheers, the ='s before the vlookup's needed removing but otherwise did the job

"Atishoo" wrote:

=IF(ISERROR(=VLOOKUP(A3, ELMAX, 2,)),"0",(=VLOOKUP(A3, ELMAX, 2,)))


"GD" wrote:

Hi, i have a lookup i'm currently using; "=VLOOKUP(A3, ELMAX, 2,)" I want to
expand this formula to include an IF(ISNA aspect as when the value being
looked up doesn't occur in the lookup table i am getting #N/A!'s and want to
have them come through as zeros...any thoughts?

Are you sure about those equals signs in the middle of the formula? Have
you tried it?
Did you perhaps intend to say
=IF(ISERROR(VLOOKUP(A3, ELMAX, 2,)),"0",(VLOOKUP(A3, ELMAX, 2,))) ?

Also, are you sure that you want a text string of "0". Isn't it more likely
that the OP wanted the *number* zero?
Perhaps =IF(ISERROR(VLOOKUP(A3, ELMAX, 2,)),0,(VLOOKUP(A3, ELMAX, 2,))) ?

And if you want to distinguish between NA() and other types of error,
wouldn't it be better to use ISNA as the OP suggested,
so perhaps =IF(ISNA(VLOOKUP(A3, ELMAX, 2,)),0,(VLOOKUP(A3, ELMAX, 2,))) ?
--
David Biddulph

"Atishoo" wrote in message
...
=IF(ISERROR(=VLOOKUP(A3, ELMAX, 2,)),"0",(=VLOOKUP(A3, ELMAX, 2,)))


"GD" wrote:

Hi, i have a lookup i'm currently using; "=VLOOKUP(A3, ELMAX, 2,)" I want
to
expand this formula to include an IF(ISNA aspect as when the value being
looked up doesn't occur in the lookup table i am getting #N/A!'s and
want to
have them come through as zeros...any thoughts?

I'm making a lot of assumptions here...

Judging by your formula and that you want a 0 returned for errors...I'm
assuming your formula normally returns a number and that your table ELMAX is
in fact sorted in ascending order.

=LOOKUP(1E100,CHOOSE({1,2},0,VLOOKUP(A3,ELMAX,2)))

Or, based on your formula, the only time you'll get a #N/A error is if A3 is
less than the min value of column 1 of ELMAX.

=IF(A3<MIN(INDEX(ELMAX,,1)),0,VLOOKUP(A3,ELMAX,2))

--
Biff
Microsoft Excel MVP


"GD" wrote in message
...
Hi, i have a lookup i'm currently using; "=VLOOKUP(A3, ELMAX, 2,)" I want
to
expand this formula to include an IF(ISNA aspect as when the value being
looked up doesn't occur in the lookup table i am getting #N/A!'s and want
to
have them come through as zeros...any thoughts?