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.