=IF(ISNA + VLOOKUP?

[GD]

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?

 

[Atishoo]

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

 

[GD]

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

 

[David Biddulph]

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,))) ?

 

[T. Valko]

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))