How To Assign A Value Based On A Number Range In Excel

[shamiq]

Hi guys, I have a set of data as shown below which has from and to range and a corresponding value-

Input Data
Range From	Range To	Value
0.00	46.35	3.8707
46.35	72.17	2.4149
72.17	458.61	2.4149
458.61	486.38	4.2
486.38	631.56	10.1

I want to compare from and to range from below table and enter the value if the range is in between interval as shown below-

Range From	Range To	Required Answer
0.00	46.35	3.8707
46.35	50.89	2.4149
50.00	50.89	2.4149
50.89	72.17	2.4149
72.17	72.17	2.4149
72.17	458.61	2.4149
458.61	486.38	4.2
486.38	580.00	10.1
580.00	631.56	10.1

Please help me out in finding a solution, I have loads of similar data.

 

[Peter_SSs]

Welcome to the MrExcel board!

If your Input Data is arranged with the 'From' column in ascending order (as yours is) and there are no overlapping ranges anywhere then try this.

21 06 03.xlsm
	A	B	C
1	Input Data
2	Range From	Range To	Value
3	0	46.35	3.8707
4	46.35	72.17	2.4149
5	72.17	458.61	2.4149
6	458.61	486.38	4.2
7	486.38	631.56	10.1
8
9
10	Range From	Range To	Value
11	0	46.35	3.8707
12	46.35	50.89	2.4149
13	50	50.89	2.4149
14	50.89	72.17	2.4149
15	72.17	72.17	2.4149
16	72.17	458.61	2.4149
17	458.61	486.38	4.2
18	486.38	580	10.1
19	580	631.56	10.1
Lookup

Cell Formulas
Range	Formula
C11:C19	C11	=VLOOKUP(A11,A$3:C$7,3)