# How to check if a certain range of numbers falls in a specified fixed range of numbers?

##### New Member
Hello there,

I need some help getting information from my data.

Lets say I have a number range in excel that I want to check what range it falls under and indicate it with some value or number or text.

Starting Ending

21.28 39.62
39.78 64.93
122.14 142.28
142.69 167.81
1580 1610
3249 3300

I want to check if my starting and ending points fall in the below category and indicate which range it falls to with (numbers, text etc) or anything that I can use later.

For example,
39.78 to 64.93 falls in S.N (1) Range and so the formula should indicate it by telling us that it falls in that category 1.
122.14 to 142.28 also falls in S.N (1) Range but max value is limited to 152.62 only
1580 to 1610 does not fall in any range and so the formula should return a false value or no value

S.N Range (Starting) Range (Ending)

1 0 152.62
2 396.27 610.16
3 680.52 804.28
4 888.22 1039.66
5 1524.08 1617.37
6 2037.18 2240.69
7 2692.1 2954
8 3232.55 3432.83
9 3780.27 3974.62

##### New Member
Thank you very much for you effort. However, this new revised formula also doesn't seem to work in my excel file. I am sharing a mini sheet. Please check it once if am doing anything wrong here. I have posted all 3 formulas here . The first working formula, 2nd and the final revised formula. I am happy with the 1st formula but just trying to make it a little bit more accurate.

Test.xlsx
2StartEndBeginningEndingIndentificatoinRevised formulaPrevious FormulaWorking Formula (1st one)
321.2839.620152.621#N/A#N/A1
439.7864.93396.27610.162#N/A#N/A1
5  680.52804.283#N/A#N/A#N/A
6  888.221039.664#N/A#N/A#N/A
7122.14142.281524.081617.375#N/A#N/A1
9  2692.129547#N/A#N/A#N/A
12  4047.154273.5110#N/A#N/A#N/A
13  4738.914836.6711#N/A#N/A#N/A
14  4959.455100.7612#N/A#N/A#N/A
15  5251.455322.9713#N/A#N/A#N/A
16  5404.435589.5114#N/A#N/A#N/A
17  5686.575800.0915#N/A#N/A#N/A
18  5884.446074.116#N/A#N/A#N/A
19  6135.026246.2117#N/A#N/A#N/A
20560.48584.316920.257046.1418002
21585.54604.447149.837229.8619002
22  7382.547670.4620#N/A#N/A#N/A
23  7749.247865.1721#N/A#N/A#N/A
24  8061.018239.9622#N/A#N/A#N/A
25  8345.098422.1523#N/A#N/A#N/A
26  9187.049309.5924#N/A#N/A#N/A
27  9387.599563.425#N/A#N/A#N/A
28  9961.9910185.626#N/A#N/A#N/A
29  10268.8510379.5827#N/A#N/A#N/A
30  10929.0111032.5428#N/A#N/A#N/A
31936.12957.5411360.9311498.3329Not in any rangeNot in any range4
32958.42982.511953.212107.5230Not in any rangeNot in any range4
Cell Formulas
RangeFormula
Y3:Y32Y3=IF(X3="Turning",B3,"")
Z3:Z32Z3=IF(X3="Turning",C3,"")
AE3:AE32AE3=IF(AND(Y3<=Z3,LOOKUP(Y3,\$AB\$3:\$AB\$52,\$AA\$3:\$AA\$52)=IFERROR(LOOKUP(Z3,\$AC\$3:\$AC\$52,\$AA\$4:\$AA\$53),1)),LOOKUP(Y3,\$AB\$3:\$AB\$52,\$AA\$3:\$AA\$52),IF(OR(LOOKUP(Y3,\$AB\$3:\$AB\$52,\$AA\$3:\$AA\$52)=IFERROR(LOOKUP(Y3,\$AC\$3:\$AC\$52,\$AA\$3:\$AA\$52),0)+1,LOOKUP(Z3,\$AC\$3:\$AC\$52,\$AA\$3:\$AA\$52)=IFERROR(LOOKUP(Z3,\$AB\$3:\$AB\$52,\$AA\$3:\$AA\$52),0)+1),"Partial","Not in any range"))

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

1,148,276
Messages
5,745,806
Members
423,976
Latest member

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