# Return a value to a cell depending its range

#### Garjo

##### New Member
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### AlanY

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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

##### Active Member
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

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

Replies
3
Views
182
Replies
5
Views
224
Replies
2
Views
265
Replies
9
Views
131
Replies
1
Views
49

1,129,769
Messages
5,638,246
Members
417,016
Latest member
Tegguy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back