Freight Cost Calc. based on Transport and Toll Cost with some criteria

Hello all,

I am trying to calc. the Total Freight from two different sheet with different data.

1. Transport data sheet - contains the item type, zip-codes, weights and prices. Extra condition here is, if the item type is 'Palette', then the minimum weight applicable should be 150kg. And the price is calculated based on the zip-codes and the weights. here is the code I used for this:

=IFERROR(IF(B3="Not Palette",INDEX('Transport Data'!C3:CS10,MATCH(B4,'Transport Data'!C2:CS2,FALSE),MATCH(B6,'Transport Data'!B3:B10,FALSE)),INDEX('Transport Data'!C12:CS54,MATCH(B4,'Transport Data'!C2:CS2,FALSE),MATCH(B6,'Transport Data'!B12:B54,FALSE))),"Palette Weight should be ≥150kg")

2. Toll data sheet: contains distances, weights and prices. Here the toll cost is determined from the weight and distance. there are two extra condition to this:

a) if the shipment type is 'Express' and weight >100kg, add 15% to total toll cost. If it is <100kg, just add 15€ to toll cost.

b) if the goods type is 'Hazardous' add extra 19€ to toll charge.

Total freight cost now should be transport cost + toll cost.

See the file attached.

Kind regards,

Foday.

Dear Carim,

Targospeed is the same as Express Shipment. It is adding 15% if 'Express Shipment' is selected and 'weight' >100kg; or just adding 15 € f it is 'Express Shipment' and 'weight' <100kg.

thank you.

Dear Carim,

many thanks for the effort an help.

the formula seems to work perfectly when Express Shipment is selected in all situations. But when Express Shipment is not selected, it does not bring up any value. And when Express Shipment is not selected but Hazardous is selected, it only brings up the charges for Hazardous an the basic cost is not included.

Many thanks for the effort again.

Thank you Carim. It is fine and works perfectly now.

Thanks a lot.

Foday.

Thank you.

Best regards,

Foday.