Formula

[Workin girl]

I have entered the below formula into my spreadsheet but get a #NA. What am
I doing wrong??

=LOOKUP(SUM(E38:E41),{4,7,11,15,19},{"1","2","3","4","5"})

 

[Jacob Skaria]

If the sum if less than 4 then the formula will return an error.

Either add one more element to the lookup array as below
=LOOKUP(SUM(E38:E41),{0,4,7,11,15,19},{"","1","2","3","4","5"})

OR handle that using I
=IF(SUM(E38:E41)>3,LOOKUP(SUM(E38:E41),{4,7,11,15,19},{"1","2","3","4","5"}),"")


If this post helps click Yes

 

[David Biddulph]

You haven't told us what value SUM(E38:E41) returns, but Excel help for the
LOOKUP function says:
"If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP
gives the #N/A error value."

 

[JBeaucaire]

3.5 is > 3 and still not high enough to avoid the error state with that
formula. Also, is there some reason you're converting the responses 1-5 into
text strings by putting them in quotes? Perhaps this instead:

=IF(SUM(E38:E41)<4, "", LOOKUP(SUM(E38:E41), {4,7,11,15,19}, {1,2,3,4,5}))

Does that help?