=IF(ISNA + VLOOKUP?

  • Thread starter Thread starter GD
  • Start date Start date
G

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