Index & Match Formula to Get Value

Here are 2 options, the first will only work if you have the FILTER function available.

Formula:

=INDEX(FILTER(Rates!$A$1:$O$2000,(B2=Rates!$A$1:$A$2000)*(D2=Rates!$B$1:$B$2000)*(G2=Rates!$D$1:$D$2000)*(I2=Rates!$F$1:$F$2000),0),, MATCH(E2,Rates!$3:$3))

Formula:

=INDEX(Rates!$A$1:$O$2000,SUMPRODUCT(ROW($A$1:$A$2000)* (B2=Rates!$A$1:$A$2000)*(D2=Rates!$B$1:$B$2000)*(G2=Rates!$D$1:$D$2000)*(I2=Rates!$F$1:$F$2000)), MATCH(E2,Rates!$3:$3))

Note, both will produce errors but that's down to the data not the formulas.

For example, on the Weight sheet you have St-Constant in the destination column and on the Rates sheet you have St. Constant.

worksheet weight K2 array formula

HTML Code: 

Add Origin Prov , formula as below

HTML Code: 

In K2 then copied down

=SUMPRODUCT((Rates!$D$4:$D$1462=$G2)*(Rates!$E$4:$E$1462=$H2)*(Rates!$F$4:$F$1462=$I2)*(Rates!$G$4:$G$1462=$J2)*(Rates!$A$4:$AB$1462=$B2)*(Rates!$B$4:$B$1462=$D2)*(INDEX(Rates!$H$4:$O$1462,,MATCH($E2,Rates!$H$3:$O$3,1))))