Need help to find number between multiple range

[spycein]

Hello Everyone,
I have a following data table

Batch No.​	Starting​	Ending​
1 to 10​	1​	10​
11 to 20​	11​	20​
21 to 30​	21​	30​
31 to 40​	31​	40​

I am looking for a formula which would return the matched Batch no. depend on input value.
For example, if i put 15 then the result should be 11 to 20, if i put 39 then the result should be 31 to 40.
Many thanks in advance.
Best Regards,

 

[shaowu459]

LOOKUP is what you need

Book1.xlsx
	A	B	C
1	Batch No.	Starting	Ending
2	1 to 10	1	10
3	11 to 20	11	20
4	21 to 30	21	30
5	31 to 40	31	40
6
7		15
8		11 to 20
Sheet3

Cell Formulas
Range	Formula
B8	B8	=LOOKUP(B7,{1;11;21;31},{"1 to 10";"11 to 20";"21 to 30";"31 to 40"})

 

[spycein]

LOOKUP is what you need

Book1.xlsx
	A	B	C
1	Batch No.	Starting	Ending
2	1 to 10	1	10
3	11 to 20	11	20
4	21 to 30	21	30
5	31 to 40	31	40
6
7		15
8		11 to 20
Sheet3

Cell Formulas
Range	Formula
B8	B8	=LOOKUP(B7,{1;11;21;31},{"1 to 10";"11 to 20";"21 to 30";"31 to 40"})

Thank you so much @shaowu459.
Best Regards,

 

[Peter_SSs]

Hello Everyone,
I have a following data table

Batch No.​	Starting​	Ending​
1 to 10​	​ 1​	​ 10​
11 to 20​	​ 11​	​ 20​
21 to 30​	​ 21​	​ 30​
31 to 40​	​ 31​	​ 40​

If you have that table, then

20 05 17.xlsm
	A	B	C
1	Batch No.	Starting	Ending
2	1 to 10	1	10
3	11 to 20	11	20
4	21 to 30	21	30
5	31 to 40	31	40
6
7		15
8		11 to 20
Batch

Cell Formulas
Range	Formula
B8	B8	=LOOKUP(B7,B2:B5,A2:A5)