I'm currently trying to find a way to return a numeric value from a list of betweens. For example, the costs relating to transport cost x amount if less than 100kg, y if between 100 and 200, z if between 200 and 300 and so on. Is there a way to do this in excel, without doing a very lengthy if formula?
If not, I'm not sure how to go about the IF formula, as it contains too many values for excel - I have about 20 possible results.
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.
