Match or Index or Lookup???
 

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

Match or Index or Lookup???
 

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)

"Qld Help.." <Qld wrote in message
...
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

Match or Index or Lookup???
 

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Qld Help.." <Qld wrote in message
...
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

Match or Index or Lookup???
 

On thinking about this, I bet it is a homework assignment!
No one would actually use a table like that!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Qld Help.." <Qld wrote in message
...
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

Match or Index or Lookup???
 

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.

"Bernard Liengme" wrote:

On thinking about this, I bet it is a homework assignment!
No one would actually use a table like that!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Qld Help.." <Qld wrote in message
...
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