Logical test to return one of three values

[Jim McAward]

Folks,
I have a problem to solve: I need a logical test to return one of
three possible solutions. I'll describe it in text, since I can't get
the code straight!! ;-)

I am looking at the value in cell A1. If A7 is between 0 and 7, then I
want to return "7". If the value is between 7 and 12, I want to return
"12". If the value is between 12 and 17, I want to return "17".

Using the logical test and the helpful solution in a recent thread, I
can accomplish two-thirds of this:

=IF(AND(A1<7,A1<12),7,12)

However, I can't test for the range above 12 and under 17!

Any help would be much appreciated!!
Best regards/Cheers,
Jim McAward
(designing a battery selection tool for batteries that come in three
sizes)

 

[Peo Sjoblom]

Try this instead Jim

=IF(A1="","",LOOKUP(A1,{0;7;12},{7;12;17}))

note that if A1 is >17 it will still return 17, if you want blank if greater
than 17

=IF(OR(A1="",A1>17),"",LOOKUP(A1,{0;7;12},{7;12;17}))

 

[Jim McAward]

Amazing!! Worked like a charm. I had cribbed some of the code from
your response to Binky, Peo... which got me much of the way there.
Your reply to me worked the rest of the way. Many thanks for your kind
assistance!
Jim McAward

 

[Peo Sjoblom]

My Pleasure..