Freight Calculation

[tr2usa]

I am preparing a freight sheet calculator for the buyers in the
company. When they put their Origin in sheet 1 and weight I would like
get the calculation from Sheet2 and bring the lowest cost with name of
the forwarder info to the sheet1 with carrier name and total cost. Can
anybody help me?
I do have the following info on Sheet1.
A B C
D
Origin Weight-Kg Forwarder Amount
Ningbo 55 Fedex 229.6


I do have the following info on Sheet2
A B C D
E F
Forwarder Origin 50kg 60kg fee Trucking
DHL Ningbo $3.60 $3.40 $25.00 $0.12
DHL Shanghai $3.50 $3.25 $25.00 $0.12
Fedex Shanghai $3.50 $3.25 $25.00 $0.25
Fedex Ningbo $3.60 $3.40 $25.00 $0.25


Thanks

 

[BriSwy]

I'm not quite sure what the 50kg and 60kg columns represented, so I set my
formula up based off of the 50kg implying 50 to 60 and 60kg implying 60kg and
above.

In cell G2 on Sheet2, paste this formula and copy it down through G5:

=IF(B2=Sheet1!$A$2,(Sheet1!$B$2*IF(Sheet1!$B$2>=60,D2,C2))+Sheet2!E2+(Sheet1!$B$2*Sheet2!F2),"")

Then, on Sheet1 in cell C2, the formula (for Forwarder) will be:

=INDEX(Sheet2!$A$2:$G$5,MATCH(MIN(Sheet2!$G$2:$G$5),Sheet2!$G$2:$G$5,FALSE),1)

While the formula in D2 (Total Cost) on Sheet1 will be:

=INDEX(Sheet2!$A$2:$G$5,MATCH(MIN(Sheet2!$G$2:$G$5),Sheet2!$G$2:$G$5,FALSE),7)

Hope this helps (you can modify the logic for the weights accordingly).