I have shipment data on one sheet and Rate data on next sheet.
On "Data" Sheet, I need "Audited Rate" in Column Y, based on following matches
1. Column J - "Shipper City"
2. Column P - "Consignee City"
3. Column V - "Billed Wgt"
3a - We need to match Shipper City (Column J)+ Consignee City (Column P) & have to calculate "Audited Rate" (Column Y)based on "Billed Weight" (Column V)of individual shipment.
3b - We have weight brackets in next sheet "Rates"
Based on given weight, we have to find appropriate rate from weight bracket from "Rates" sheet.
Weight Brackets are as follow on "Rates" Sheet.
Weight Brackets MIN Value
1 - 499 LBS 0
500 - 999 LBS 500
1,000 - 1,999 LBS 1000
2,000 - 4,999 LBS 2000
5,000 - 9,999 LBS 5000
10,000 - 19,999 LBS 10000
20,000 + LBS 20000
MINIMUM CHARGE
I need "MIN Charge" in Column Z : Based on below matches
1. Column J - "Shipper City"
2. Column P - "Consignee City"
Match both "Shipper City" & "Consignee City" and based on it find the FROM "Rates" sheet under "MINIMUM Charge" Value (Column N)
EXAMPLE
Shipper City Consignee City Min Charge
BURNABY CALGARY 38.68
Will you please help me to get the "Audited Rate" based on INDEX+MATCH or any kind of VLOOKUP Formula.
Thanks a lot in advance for all your help.
Regards,
Bookmarks