Return a value to a cell depending its range

[Garjo]

I'm hoping someone has the answer.

I Have a value within a cell to which I would like to check against a number of ranges and return a new value a shown below.

14 = 1

<tbody>
</tbody>

Less than 25 =	1 ​
Between 25-75 =	2 ​
Between 75-150 =	3​
Between 150-250 =	4 ​
Above 250 =	5​

<tbody>
</tbody>

Which fuction should I be using within my formula

 

[AlanY]

you can use a nested if() function like this, let say the value is in A1

-if(A1<25,1,if(A1<=75,2,if(A1<=150,3,if(A1<=250,4,5))))

 

[Rick Rothstein]

Assuming your values are never negative, you can also use this formula...

=LOOKUP(A1,{0,26,76,151,251},{1,2,3,4,5})

 

[Garjo]

Cheers Guys that works a treat the only issues im getting if the range is 0,1.11,1.12..... I always get a number 5 when using the lookup function

 

[Phuoc]

Assuming your values are never negative, you can also use this formula...

=LOOKUP(A1,{0,26,76,151,251},{1,2,3,4,5})

a little shorter:

=MATCH(A1,{0,26,76,151,251})

 

[Garjo]

Sorry my misstake i was pointing to the wrong cell. Thankyou for taking the time to help.