function to matching the zone and calculate the rate of shipping

[nhanle1492]

Hi, I am getting stuck at doing he index match function to matching the zone and find out the rate of shipping between 2 tables:
In the table 1 i have several shipments information like the zone and the actual ship weight. the table 2 is the rate of list of weight from 1-150lb and zone
Please help for this

[Pete_UK]

You can use this formula in C2:

=IF(A2>150,A2*HLOOKUP(B2,$T$3:$AC$5,2,0),INDEX($G$2:$P$151,MATCH(A2,$F$2:$F$151,0),MATCH(B2,$G$1:$P$1,0)))

then copy down as required.

Hope this helps.

Pete

[nhanle1492]

Hi Pete, Thank you so much for your instruction, I got the error, instead, mine is error a bit, instead it match 26 with appropriate cost but mine match with 25 and get lower 1 decimal for freight so they make the wrong cost, any suggestion for this one?

[CARACALLA]

C2=SUMPRODUCT(($F$2:$F$151=$A2)*($G$1:$P$1=$B2)*($G$2:$P$151))

Copy down

[nhanle1492]

Another way to learn, thank you so much Caracalla

[CARACALLA]

You are welcome