=IF(ISNA + VLOOKUP?

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top