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
Package 5 Road 11
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

1,148,283
Messages
5,745,842
Members
423,981
Latest member
ph1l

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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