# Values in Multipule Ranges

#### lawdog

##### New Member
I am having problems writing a formula that will return a different number based on were a number falls in different ranges. Below is an example<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
499 500 400<o></o>
399 300<o></o>
299 200<o></o>
199 100<o></o>
<o> </o>
If the number in the first column fall in range one then 3, if range second range then 2, if the third range then 1. Any ideas?<o></o>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### jbeaucaire

##### Well-known Member
I am having problems writing a formula that will return a different number based on were a number falls in different ranges. If the number in the first column fall in range one then 3, if range second range then 2, if the third range then 1. Any ideas?

Your question seems to imply you're looking for a value between ranges of numbers, but then your sample data did not layout in clearly separated "ranges". Your data implied "exact match" type of data. So which is it? I reoriented the data to a more standard "range" format

Let's see if I was able to decipher your chart properly:

Code:
`````` A       B      C
500             1
400     499     2
300     399     3
200     299     4
100     199     5``````

You can lookup any value in that table and get the value from column C returned with a simple formula. For instance, if you had a value in cell E1 of 325, this formula would return a value of "3":

=LOOKUP(E1,\$A\$1:\$A\$5,\$C\$1:\$C\$5)

#### lawdog

##### New Member
A B C
345 500-400
399-300
299-200
What I am trying to do is write a formual that returns a 1 2 or 3 based on which range (the numbers in B and C) the number in A falls in.

#### jbeaucaire

##### Well-known Member
A B C
345 500-400
399-300
299-200
What I am trying to do is write a formual that returns a 1 2 or 3 based on which range (the numbers in B and C) the number in A falls in.

Those are not standard formats. Used like so, the formula below will work:
Excel Workbook
ABCDE
134520032
23002
34001
Sheet1

The yellow numbers represent the START of each range. When you start the next range, it provides the "top" of the first range automatically, so you don't need to show 200-299, just show 200 as the first start, then 300 as the next.

The blue numbers are the numbers you want to "match" for each range starting with the number in yellow.

The Green cell has the formula you wanted.

Replies
4
Views
214
Replies
5
Views
176
Replies
2
Views
668
Replies
11
Views
574
Replies
2
Views
233

1,191,707
Messages
5,988,223
Members
440,139
Latest member
ngaicuong2017

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