Match or Index or Lookup???

[Qld Help..]

I want to find the weight (A3 570) within the table (D1:K3), and return the
$/kg rate multipled by the weight, plus the Base. I'm struggling on using a
match/ index function, should I just be using a lookup function or both?

A B C D E F G H I
J K
1 1 250 251 500 501 1000
1001 3000
2 Base $/kg Base $/kg Base $/kg Base
$/kg
3 570 25 0.118 35 0.083 40 0.055 50
0.053
4
5

 

[Bob Phillips]

I am struggling to understand the data. What result numbers would be used in
this calculation if doing it on paper?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

In D1:K3 I have 1, 250, 251, 500, 501, 750,751, 1000
In D3:K3: I have 25, 0.118, etc

In A3 I have 570
In B3 I used =MATCH(INT(A3/250)*250,D1:K1,1) --- this gives 4 meaning the
fourth element in D1:K1
In B4 I used =INDEX(D3:K3,B3) --- gives me 0.083 for the $/kg
In B5 I used =INDEX(D3:K3,B3-1) --- gives me 35 for the base
I will let you combine the three formulas to get the answer

But it would have bee much easier if row 2 has the $/kg and row 3 had the
base!
Ie the data one under the other rather than side by side for each Kg value
Then we could use HLOOKUP
best wishes

 

[Bernard Liengme]

On thinking about this, I bet it is a homework assignment!
No one would actually use a table like that!

 

[Qld Help..]

No this is not a homework assignment, Im trying to understand how and where
to apply a match/ index in work. Thanks for your help.