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

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Please help!
 

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
YZAAABACADAEAF
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
8142.69167.812037.182240.696#N/A#N/ANo High Grade
9  2692.129547#N/A#N/A#N/A
10202.11222.993232.553432.83800No High Grade
11223.33244.983780.273974.62900No High Grade
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,"")
AD3:AD32AD3=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(SUMPRODUCT((Y3>=$AB$3:$AB$52)*(Y3<=$AC$3:$AC$52))+SUMPRODUCT((Z3>=$AB$3:$AB$52)*(Z3<=$AC$3:$AC$52)),"Partial","Not in any range"))
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"))
AF3:AF32AF3=IF(AND(Y3<=Z3,LOOKUP(Y3,$AA$3:$AA$52,$AC$3:$AC$52)=IFERROR(LOOKUP(Z3,$AB$3:$AB$52,$AC$4:$AC$53),1)),LOOKUP(Y3,$AA$3:$AA$52,$AC$3:$AC$52),"No High Grade")
 

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

Forum statistics

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

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top