Using lookup instead of if-statements, how?

[Loranga]

Aloha,

Here is screen where I try to explain my problem:
http://hem.bredband.net/maromb/example.html

On sheet 1 I have a ranking list of with diffrent cities, haulier an
differnt cost/weight.
Sheet1
Weight
A | B | C | D |
City | Haulier | 0-2,5 | 5-7| 7-10...
____________________________
City1|Haulier1 | 100 | 95| 80
City1|Haulier2 | 110 | 100| 90
City1|Haulier3 | 120 | 110| 91
City2|Haulier1 | 95 | 90| 87
City2|Haulier2 | 105 | 100| 89
City2|Haulier3 | 107 | 105| 88

On sheet2 I have made a VBA User Form where I input city, haulier an
the weight.

A | B | C | D
City | Haulier | Weight | cost/weight
City2| Haulier2| 6 | ?

What I would like to do is make a formula in the D collum that chec
City, Haulier and weight and then returns the correct price/ton. I
this case the ? should be 100. I almost got this to work with
function of 6 if statements and sumproduct.
But there must be some easier way of doing this with some type o
lookup formula, right?


http://hem.bredband.net/maromb/example.htm

 

[Frank Kabel]

Hi
one way:
1. change the heading row 1 for the weights so that they show only the
lower limit. e.g. change
0-2,5
to
0

2. Now use the following array formula on your second sheet (entered
with CTRL+SHIFT+ENTER):
=INDEX(OFFSET('sheet1'!$C$1:$C$100,0,MATCH(C2,'sheet1'$C$1:$G$1,1)-1),M
ATCH(1,('sheet1'!$A$1:$A$100=A2)*('sheet1'!$B$1:$B$100=B2,0))

 

[Loranga]

Thanks for the help!
But I just solved this with a "homemade" VBA function