You can return a value from a range of data by defining your ranges and corresponding values in a table and running a VLOOKUP function with the closest match logical value set to TRUE.
The following example shows the defined values in columns E and F, the raw data in column B and the formula to make the match in column C.
Excel Spreadsheet:
A
B
C
D
E
F
G
1
2
Weight
Transport
Min
Value
3
122
=VLOOKUP(B3,$E$3:$F$6,2,TRUE)
0
15
4
245
=VLOOKUP(B4,$E$3:$F$6,2,TRUE)
100
30
5
253
=VLOOKUP(B5,$E$3:$F$6,2,TRUE)
200
45
6
360
=VLOOKUP(B6,$E$3:$F$6,2,TRUE)
300
60
7
The formula from cell C3 can be copied down to cells C4 to C6.
