weight check to see price

HellGuy

New Member
Joined
Feb 3, 2014
Messages
2
I would like create a table where i can just input the weight and zone of the product and see it is cost.

I have weight ranges like; first two cloums are weight range and next 3 are the prices based on the zones.
31405,70 ₺6,10 ₺6,50 ₺
41505,75 ₺6,25 ₺6,75 ₺
51606,35 ₺6,95 ₺7,55 ₺
61706,40 ₺7,10 ₺7,80 ₺
71806,45 ₺7,25 ₺8,05 ₺
81906,50 ₺7,40 ₺8,30 ₺
911006,55 ₺7,55 ₺8,55 ₺
1011108,80 ₺9,90 ₺11,00 ₺
1111208,85 ₺10,05 ₺11,25 ₺
1211308,90 ₺10,20 ₺11,50 ₺
1311408,95 ₺10,35 ₺11,75 ₺

<tbody>
</tbody>

<tbody>
</tbody>

So i need the formula to check where the written weight in first two colums and check correct zone and bring up the right cost for that.

Can you help me on this one?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

HellGuy

New Member
Joined
Feb 3, 2014
Messages
2
I got it sorted :)

=INDEX(!$E$1:$H$201;MATCH(C2;!$E$1:$E$201;1);MATCH(D2;!$E$1:$H$1;0))
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Maybe this.

With your table in A3:E13.

A Drop Down in G1 with a list of 3,4,5

Hand type in the weight in G2.

And this formula in G3.

=IF(G2<31,"Below min wt.",VLOOKUP(G2,A3:E13,G1,10))

Type in the weight, and select the zone and the formula shows the cost.

Regards,
Howard
 

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
ZoneZoneZone
FromTo123
31405.70 £6.10 £6.50 £
41505.75 £6.25 £6.75 £wheigtzonePrice
51606.35 £6.95 £7.55 £140311.75 £=SUMPRODUCT((G5>=A3:A13)*(G5<=B3:B13)*(H5=C2:E2)*C3:E13)
61706.40 £7.10 £7.80 £
71806.45 £7.25 £8.05 £
81906.50 £7.40 £8.30 £
911006.55 £7.55 £8.55 £
1011108.80 £9.90 £11.00 £
1111208.85 £10.05 £11.25 £
1211308.90 £10.20 £11.50 £
1311408.95 £10.35 £11.75 £
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" span="5"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="64" style="width: 48pt;" span="3"> <col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;"> <col width="496" style="width: 372pt; mso-width-source: userset; mso-width-alt: 18139;"> <tbody> </tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,081
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top