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

[erbhaskar95]

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]

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
	Y	Z	AA	AB	AC	AD	AE	AF
2	Start	End	Beginning	Ending	Indentificatoin	Revised formula	Previous Formula	Working Formula (1st one)
3	21.28	39.62	0	152.62	1	#N/A	#N/A	1
4	39.78	64.93	396.27	610.16	2	#N/A	#N/A	1
5			680.52	804.28	3	#N/A	#N/A	#N/A
6			888.22	1039.66	4	#N/A	#N/A	#N/A
7	122.14	142.28	1524.08	1617.37	5	#N/A	#N/A	1
8	142.69	167.81	2037.18	2240.69	6	#N/A	#N/A	No High Grade
9			2692.1	2954	7	#N/A	#N/A	#N/A
10	202.11	222.99	3232.55	3432.83	8	0	0	No High Grade
11	223.33	244.98	3780.27	3974.62	9	0	0	No High Grade
12			4047.15	4273.51	10	#N/A	#N/A	#N/A
13			4738.91	4836.67	11	#N/A	#N/A	#N/A
14			4959.45	5100.76	12	#N/A	#N/A	#N/A
15			5251.45	5322.97	13	#N/A	#N/A	#N/A
16			5404.43	5589.51	14	#N/A	#N/A	#N/A
17			5686.57	5800.09	15	#N/A	#N/A	#N/A
18			5884.44	6074.1	16	#N/A	#N/A	#N/A
19			6135.02	6246.21	17	#N/A	#N/A	#N/A
20	560.48	584.31	6920.25	7046.14	18	0	0	2
21	585.54	604.44	7149.83	7229.86	19	0	0	2
22			7382.54	7670.46	20	#N/A	#N/A	#N/A
23			7749.24	7865.17	21	#N/A	#N/A	#N/A
24			8061.01	8239.96	22	#N/A	#N/A	#N/A
25			8345.09	8422.15	23	#N/A	#N/A	#N/A
26			9187.04	9309.59	24	#N/A	#N/A	#N/A
27			9387.59	9563.4	25	#N/A	#N/A	#N/A
28			9961.99	10185.6	26	#N/A	#N/A	#N/A
29			10268.85	10379.58	27	#N/A	#N/A	#N/A
30			10929.01	11032.54	28	#N/A	#N/A	#N/A
31	936.12	957.54	11360.93	11498.33	29	Not in any range	Not in any range	4
32	958.42	982.5	11953.2	12107.52	30	Not in any range	Not in any range	4
Package 5 Road 11

Cell Formulas
Range	Formula
Y3:Y32	Y3	=IF(X3="Turning",B3,"")
Z3:Z32	Z3	=IF(X3="Turning",C3,"")
AD3:AD32	AD3	=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:AE32	AE3	=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:AF32	AF3	=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")