Formula
 

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

Formula
 

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 IF
=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
---------------
Jacob Skaria


"Workin girl" wrote:

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

Formula
 

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."
--
David Biddulph

"Workin girl" wrote in message
...
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"})

Formula
 

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?
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jacob Skaria" wrote:

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 IF
=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
---------------
Jacob Skaria


"Workin girl" wrote:

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