Forumla to Find Shipping Cost from Weight

[daleyelland]

Hi all,

Having a thick moment here and can't get my head round something that I'm sure is really simple. In Sheet1, I have a table of items and the weight of the item is in column X.

In Sheet2 I have a table of shipping costs depending on weight. The table is below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112"]Item Weight Min[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="width: 114"]Item Weight Max[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]Cost[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]150[/TD]
[TD]1.66[/TD]
[/TR]
[TR]
[TD]151[/TD]
[TD]400[/TD]
[TD]1.72[/TD]
[/TR]
[TR]
[TD]401[/TD]
[TD]900[/TD]
[TD]1.77[/TD]
[/TR]
[TR]
[TD]901[/TD]
[TD]1400[/TD]
[TD]2.19[/TD]
[/TR]
</tbody>[/TABLE]

Say for example an item weighs 100 it would cost 1.66, 500 would cost 1.77 etc. I need a formula that assigns a group for each weight, eg. 100 = 0-150, and then gives the cost at the end

Many thanks

 

[VBA Geek]

Perhaps you look for this?

Excel 2010
	A	B	C	D	E	F	G
1	Item Weight Min	Item Weight Max	Cost
2	0	150	1.66			Weight:	100
3	151	400	1.72			Cost:	1.66
4	401	900	1.77
5	901	1400	2.19
Sheet4

Cell Formulas
Range	Formula
G3	=LOOKUP(G2,A2:A5,C2:C5)

 

[daleyelland]

Thank you so much!