=IF(ISNA + VLOOKUP?
 

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(ISNA + VLOOKUP?
 

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

=IF(ISNA + VLOOKUP?
 

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?

=IF(ISNA + VLOOKUP?
 

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?

=IF(ISNA + VLOOKUP?
 

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?