Spreadsheet: 734069d1621964586-need-rate-based-on-index-match-or-vlookup-formula-rate-match-file-bc
Sheet: Data

Description |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACTUAL |  | AUDITED
Invoice # | Invoice Date | Date | Month | PRO# | Type | Prefix | [NAME] | Shipper Street | Shipper City | Prov | Postal Code | [NAME] | Store | Consignee Street | Consignee City | Prov-Dest | Postalcode | LTL or TL | Pcs | Wgt | Billed Wgt | Rate | Charge | Audited Rate | MIN Charge | Charge
225970 | 2021-03-18 00:00:00 | 2021-02-01 00:00:00 | MAR | BC13844940OAB | D | I | VENDOR | 4980 KINGSWAY | BURNABY | BC | V5H | UPS |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 3 | 234 | 250 | 11.73 | 140.76 |  | 38.68 |  |  | I need Audited Rate in Column Y  "Audited Rate" based on following matches
225970 | 2021-03-18 00:00:00 | 2021-02-01 00:00:00 | MAR | BC13844942OAB | D | I | VENDOR | 4980 KINGSWAY | BURNABY | BC | V5H | UPS |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 3 | 490 | 500 | 11.73 | 168.91 |  |  |  |  | 1. Column J - "Shipper City"
225970 | 2021-03-18 00:00:00 | 2021-02-01 00:00:00 | MAR | BC13844944OAB | D | I | VENDOR | 4980 KINGSWAY | BURNABY | BC | V5H | UPS |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 1200 | 1500 | 11.73 | 117.3 |  |  |  |  | 2. Column P - "Consignee City"
225963 | 2021-03-12 00:00:00 | 2021-02-01 00:00:00 | MAR | BC12329165OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC1 |  | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | LTL | 1 | 346 | 400 | 16.36 | 91.62 |  |  |  |  | 3. Column V - "Billed Wgt"
225963 | 2021-03-12 00:00:00 | 2021-02-02 00:00:00 | MAR | BC12329200OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC1 |  | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | LTL | 1 | 2400 | 2500 | 16.36 | 89.33
225963 | 2021-03-12 00:00:00 | 2021-02-03 00:00:00 | MAR | BC12329312OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC1 |  | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | LTL | 3 | 12100 | 12300 | 8.78 | 175.6 |  |  |  |  | 3a - We need to match [NAME] (Column J)+ Consignee City (Column P) & have to calculate "Audited Rate"  (Column Y)based on "Billed Weight"  (Column V)of individual shipment.
225963 | 2021-03-12 00:00:00 | 2021-02-01 00:00:00 | MAR | BC12329162OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 2 |  | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | LTL | 1 | 360 | 400 | 19.7 | 108.94 |  |  |  |  | 3b - We have weight brackets in next sheet "Rates"
225963 | 2021-03-12 00:00:00 | 2021-02-03 00:00:00 | MAR | BC12329296OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 2 |  | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | LTL | 1 | 690 | 700 | 19.7 | 108.94 |  |  |  |  | Based on given weight, we have to find appropriate rate from weight bracket from "Rates" sheet.
225963 | 2021-03-12 00:00:00 | 2021-02-04 00:00:00 | MAR | BC12329381OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 2 |  | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | LTL | 2 | 900 | 1000 | 14.82 | 166.87 |  |  |  |  | Weight Brackets are as follow on "Rates" Sheet.
225963 | 2021-03-12 00:00:00 | 2021-02-01 00:00:00 | MAR | BC12329119OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 3 |  | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | LTL | 2 | 1800 | 1800 | 22.36 | 242.83 |  |  |  |  | Weight Brackets | MIN Value
225963 | 2021-03-12 00:00:00 | 2021-02-02 00:00:00 | MAR | BC12329240OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 3 |  | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | LTL | 2 | 50 | 50 | 22.36 | 235.45 |  |  |  |  | 1 - 499 LBS | 0
225963 | 2021-03-12 00:00:00 | 2021-02-03 00:00:00 | MAR | BC12329301OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 3 |  | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | LTL | 2 | 470 | 600 | 22.36 | 247.3 |  |  |  |  | 500 - 999  LBS | 500
225963 | 2021-03-12 00:00:00 | 2021-02-01 00:00:00 | MAR | BC12329158OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 4 |  | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | LTL | 1 | 1800 | 2000 | 16.36 | 81.8 |  |  |  |  | 1,000 - 1,999 LBS | 1000
225963 | 2021-03-12 00:00:00 | 2021-02-02 00:00:00 | MAR | BC12329220OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 4 |  | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | LTL | 2 | 3950 | 4000 | 11.73 | 127.39 |  |  |  |  | 2,000 - 4,999 LBS | 2000
225963 | 2021-03-12 00:00:00 | 2021-02-03 00:00:00 | MAR | BC12329309OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 4 |  | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | LTL | 3 | 7250 | 7500 | 8.78 | 175.6 |  |  |  |  | 5,000 - 9,999 LBS | 5000
225963 | 2021-03-12 00:00:00 | 2021-02-01 00:00:00 | MAR | BC12329147OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 5 |  | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | LTL | 1 | 8950 | 9000 | 16.36 | 88.34 |  |  |  |  | 10,000 - 19,999 LBS | 10000
225963 | 2021-03-12 00:00:00 | 2021-02-02 00:00:00 | MAR | BC12329228OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 5 |  | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | LTL | 1 | 18950 | 19000 | 16.36 | 89.33 |  |  |  |  | 20,000 + LBS | 20000
225963 | 2021-03-12 00:00:00 | 2021-02-04 00:00:00 | MAR | BC12329389OAB | D | I | DC | 1100 - 48 ST SE | CALGARY | AB | T2A | Store BC 5 |  | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | LTL | 15 | 21000 | 21500 | 16.36 | 88.34
 |  |  |  |  |  |  | UPS | 5855-68TH AVE SE | CALGARY | AB | T2A | VENDOR |  | 4980 KINGSWAY | BURNABY | BC | V5H | LTL | 3 | 344 | 360 | 11.73 | 140.76
 |  |  |  |  |  |  | UPS | 5855-68TH AVE SE | CALGARY | AB | T2A | VENDOR |  | 4980 KINGSWAY | BURNABY | BC | V5H | LTL | 3 | 643 | 653 | 11.73 | 168.91 |  |  |  |  | MINIMUM CHARGE
 |  |  |  |  |  |  | UPS | 5855-68TH AVE SE | CALGARY | AB | T2A | VENDOR |  | 4980 KINGSWAY | BURNABY | BC | V5H | LTL | 2 | 1450 | 1750 | 11.73 | 117.3 |  |  |  |  | I need "MIN Charge" in Column Z : Based on below matches
 |  |  |  |  |  |  | Store BC1 | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 1000 | 1054 | 16.36 | 91.62 |  |  |  |  | 1. Column J - "Shipper City"
 |  |  |  |  |  |  | Store BC1 | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 3189 | 3289 | 16.36 | 89.33 |  |  |  |  | 2. Column P - "Consignee City"
 |  |  |  |  |  |  | Store BC1 | 7250 MARKET CROSSING CRU 11B | BURNABY | BC | V5J | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 3 | 12552 | 12752 | 8.78 | 175.6 |  |  |  |  | Match both "Shipper City" & "Consignee City" and basd on it find the FROM "Rates"  sheet under  "MINIMUM Charge" Value (Column N)
 |  |  |  |  |  |  | Store BC 2 | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 981 | 1021 | 19.7 | 108.94
 |  |  |  |  |  |  | Store BC 2 | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 1443 | 1453 | 19.7 | 108.94
 |  |  |  |  |  |  | Store BC 2 | 1965 COLUMBIA AVENUE 100 | CASTLEGAR | BC | V1M | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 1851 | 1951 | 14.82 | 166.87 |  |  |  |  | EXAMPLE
 |  |  |  |  |  |  | Store BC 3 | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 2454 | 2454 | 22.36 | 242.83 |  |  |  |  | Shipper City | Consignee City | Min Charge
 |  |  |  |  |  |  | Store BC 3 | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 173 | 173 | 22.36 | 235.45 |  |  |  |  | BURNABY | CALGARY | 38.68
 |  |  |  |  |  |  | Store BC 3 | 1326 ISLAND HWY | CAMPBELL RIVER | BC | V8X | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 1124 | 1254 | 22.36 | 247.3
 |  |  |  |  |  |  | Store BC 4 | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 2679 | 2879 | 16.36 | 81.8
 |  |  |  |  |  |  | Store BC 4 | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 2 | 5474 | 5524 | 11.73 | 127.39
 |  |  |  |  |  |  | Store BC 4 | 34530 MCCONNELL ROAD | ABBOTSFORD | BC | V3R | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 3 | 13774 | 14024 | 8.78 | 175.6
 |  |  |  |  |  |  | Store BC 5 | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 18528 | 18578 | 16.36 | 88.34
 |  |  |  |  |  |  | Store BC 5 | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 1 | 20304 | 20354 | 16.36 | 89.33
 |  |  |  |  |  |  | Store BC 5 | 26310 FRASER HIGHWAY | ALDERGROVE | BC | V1Q | DC |  | 1100 - 48 ST SE | CALGARY | AB | T2A | LTL | 15 | 24325 | 24825 | 16.36 | 88.34