=lookup help

[rexy69]

hi i need to basically look at a cell and put this forumla into cell A3

if the number in cell A1 falls between 1-3.9 put number 0
if the number in cell A1 falls between 4-9.9 put number 1
if the number in cell A1 falls between 10-19.99 put number 2
if the number in cell A1 is 20+ put number 3

i can use it with whole numbers but not with ranges

can anyone help??


Thanks

[AlKey]

try this one

=LOOKUP(A1,{0,0;4,1;10,2;20,3})

[clabulis]

hi i need to basically look at a cell and put this forumla into cell A3

if the number in cell A1 falls between 1-3.9 put number 0
if the number in cell A1 falls between 4-9.9 put number 1
if the number in cell A1 falls between 10-19.99 put number 2
if the number in cell A1 is 20+ put number 3

i can use it with whole numbers but not with ranges

can anyone help??


Thanks

Assuming you mean 1-3.999999......
and 4-9.99999999......
etc

as opposed to the non-repeating numbers you posted, this may work (put it in A3):

=LOOKUP(A1,{1,4,10,20},{0,1,2,3})

[grkchakri]

Hi
See below and check if this helps , may be an alternative to lookup

=IF(AND(A1>=1, A1<=3.9),0,
IF(AND(A1>=4, A1<=9.9),1,
IF(AND(A1>=10,A1<=19.99),2,3))

[clabulis]

Careful with the if statement option. Entering a value of "9.99" or a "3.95" for example will result in a "3". It should be more along the lines of:

=IF(AND(A1>=1,A1<4),0,IF(AND(A1>=4,A1<10),1,IF(AND(A1>=10,A1<20),2,3))

[rexy69]

thanks for all the responses, just tried Clabulis' method and it worked perfectly

Many Thanks!!!!!1

REP+