Advanced lookup & comparing (of transportation costs)

[Pastafarian]

Hello,

I haven't found a similar question nor answer to this problem yet, so I'm posting it here hoping someone can help out.
I want to compare the costs of different transportation methods. These costs differ by the country, zipcode and the amount you need to send (packages or pallets).
Users would need to select the country from a dropdown list, then fill in the zipcode and the amount of pallets or packages. It should then pull the costs from the different transporting-companies from different sheets and paste them in cells for the user to compare.

Example data is as shown below:

Transporting-company 1:
[TABLE="width: 736"]
<tbody>[TR]
[TD="colspan: 2"]Transporter 1[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]# of pallets[/TD]
[TD][/TD]
[TD]43,48-50[/TD]
[TD="align: right"]41,42[/TD]
[TD]32-36[/TD]
[TD]37,39,51,54532[/TD]
[TD][/TD]
[TD]CR,ME,UB,CO[/TD]
[TD]SO,SP,M,HG[/TD]
[TD]AB,PH,CA[/TD]
[TD]DA,NW,UB[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]130[/TD]
[TD][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]254[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]285[/TD]
[TD][/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]348[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]335[/TD]
[TD][/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]435[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]376[/TD]
[TD][/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]515[/TD]
[TD="align: right"]520[/TD]
[/TR]
</tbody>[/TABLE]

Transporting-company 2:
[TABLE="width: 768"]
<tbody>[TR]
[TD="colspan: 2"]Transporter 2[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]# of pallets[/TD]
[TD][/TD]
[TD="align: right"]43,49[/TD]
[TD]41,42,48[/TD]
[TD]32-36[/TD]
[TD="align: right"]37,39,54[/TD]
[TD="align: right"]50,51[/TD]
[TD][/TD]
[TD]CR,ME,UB[/TD]
[TD]SO,SP,M,CO[/TD]
[TD]AB,PH,CA[/TD]
[TD]DA,NW,UB,HG[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]225[/TD]
[TD][/TD]
[TD="align: right"]214[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]226[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]266[/TD]
[TD="align: right"]269[/TD]
[TD="align: right"]272[/TD]
[TD][/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]356[/TD]
[TD="align: right"]358[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]307[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]317[/TD]
[TD="align: right"]322[/TD]
[TD][/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]368[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]374[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]315[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]336[/TD]
[TD][/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]441[/TD]
[TD="align: right"]449[/TD]
[/TR]
</tbody>[/TABLE]

Please note there's a difference between transporter in zones for zipcodes and the amount of 'zones'. For UK customers the prices are dispersed by the first two letters of the zipcode. For all other countries the prices are based on zipcodes in numbers, usually the first two, but sometimes it needs an exact match, like Transporter1-Germany Zone 5.

Example1: I want to transport 4 pallets to Germany to zipzode 50769 (Köln), it should get 290 Euro from Transporter 1 and 332 Euro from Transporter 2.
Example2: I want to transport 2 pallets to Germany to zipcode 54532, it should get 240 Euro from Transporter 1 and 215 Euro from Transporter 2.
Example3: I want to transport 3 pallets to the UK to zipcode CO1 2XL, it should get 330 Euro from Transporter 1 and 354 Euro from transporter 2.

So far I've got the part where I can check for the first two digits of the zipcode and get the costs based on the amount of pallets, but I can't get the zipcodes that are in a 'range' (like 32-36) to work and I also can't get it to work for UK customers.

Anybody got any idea how to tackle this problem?

Any help much appreciated!

 

[Pastafarian]

I will actually make it a bit harder:

I want to compare these prices to the prices of sending the same shipment by packages instead of pallets.
So basicly the situation becomes as follows: I have a shipment and want to know what it'll cost to ship it with Transporter 1, Transporter 2 or Transporter 3 where Transporter 1 and 2 ship it by pallets and Transporter 3 ships the packages separately. In some cases, when the shipment isn't too big, shipping it as separate packages is cheaper.

The prices for Transporter 3 are noted as:

[TABLE="width: 273"]
<tbody>[TR]
[TD][/TD]
[TD] UK[/TD]
[TD]Germany[/TD]
[TD] France[/TD]
[/TR]
[TR]
[TD]Per Package[/TD]
[TD] € 8,00[/TD]
[TD] € 6,00[/TD]
[TD] € 11,00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10 kg[/TD]
[TD] € 5,00[/TD]
[TD] € 8,00[/TD]
[TD] € 7,00[/TD]
[/TR]
[TR]
[TD="align: center"]20 kg[/TD]
[TD] € 10,00[/TD]
[TD] € 15,00[/TD]
[TD] € 14,00[/TD]
[/TR]
[TR]
[TD="align: center"]30 kg[/TD]
[TD] € 15,00[/TD]
[TD] € 20,00[/TD]
[TD] € 19,50[/TD]
[/TR]
[TR]
[TD="align: center"]40 kg[/TD]
[TD] € 18,00[/TD]
[TD] € 24,00[/TD]
[TD] € 23,40[/TD]
[/TR]
[TR]
[TD="align: center"]50 kg[/TD]
[TD] € 20,50[/TD]
[TD] € 27,50[/TD]
[TD] € 27,30[/TD]
[/TR]
[TR]
[TD="align: center"]80 kg[/TD]
[TD] € 22,00[/TD]
[TD] € 30,50[/TD]
[TD] € 31,20[/TD]
[/TR]
[TR]
[TD="align: center"]100 kg[/TD]
[TD] € 23,50[/TD]
[TD] € 33,50[/TD]
[TD] € 35,10[/TD]
[/TR]
[TR]
[TD="align: center"]150 kg[/TD]
[TD] € 25,00[/TD]
[TD] € 36,50[/TD]
[TD] € 39,00[/TD]
[/TR]
[TR]
[TD="align: center"]200 kg[/TD]
[TD] € 26,50[/TD]
[TD] € 39,50[/TD]
[TD] € 42,90[/TD]
[/TR]
[TR]
[TD]Above 200 per kg[/TD]
[TD] € 0,08[/TD]
[TD] € 0,09[/TD]
[TD] € 0,12[/TD]
[/TR]
</tbody>[/TABLE]

There is a minimal rate per package and a price based on the weight (actualy or volume weight, heighest counts). The highest amount will be invoiced. Above the 200kg there will be a certain amount per kg which is added to the 200 kg price.
Example1: 3 packages UK which weigh 35kg with volumeweight of 55kg. Package rate: 3x8 = 24 Euro. Volume-weight > actual weight, so 55 kg = 22 Euro. Price will be: 24 Euro.
Example2: 6 packages Germany which weigh 78kg with volumeweight of 212kg. Package rate: 6x6 = 36 Euro. Volume-weight > actual weight, so 212 kg = 39,50+(12*0,09) = 40,58 Euro. Price will be 40,58 Euro.

Users will input:
-The country
-The zipcode (For pallettransport by T1 and T2)
-The amount of pallets ánd the amount of packages it holds.

It should give back the costs for transporting it with Transporter 1, 2 and 3 so the user can decide with what Transporter it should transport the shipment with.


I know it's a lot to ask, so if anyone could just design a part of it, I'll adjust it to my own needs, I've been stuck on this for too long.
If requested, I can add a example workbook.


Any help appreciated!