Returning value from a range of data

[Guest]

Hi,



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.

 

[Guest]

Create a table with the breakpoints in the first column and the associated
costs in the second:
0 x
100 y
200 z
Suppose this is in Sheet2, cells A1:B3
Then, if your shipping weight is in cell A1, the shipping cost would be
=vlookup(a1,sheet2!$A$1:$B$3,2). If you have a series of items to ship,
with weights continuing in column A, you could enter this formula in B1 then
just copy from B1 down through column B; each cell in column B would
indicate the shipping cost given the weight in the cell to the left. (Note
that this will show the cost as y for anything weighing >= 100kg and <200kg;
the breakpoints are 'inclusive').
HTH. --Bruce