Spreadsheet: 695641d1600324377-index-and-match-formula-to-get-value-weights-and-rates-1-ans
Sheet: Weight

[NAME] | Carrier | ORGANISATION | Weight Density | WEIGHT | Return DC | Origin City | Origin Prov | Destination City | Destination PROV | Freight Rate
1 | CFF | FGL | 8 LBS | 693 | CDC | Calgary | AB | Wetaskiwin | AB |  |  |  | Currently, I have to slice the files multiple times, based on, Carreir Name, Weight density, Origin City and Density
1 | CFF | FGL | 8 LBS | 382 | CDC | Calgary | AB | Calgary | AB
1 | CFF | FGL | 8 LBS | 536 | CDC | Calgary | AB | Edmonton | AB
1 | CFF | FGL | 8 LBS | 1280 | CDC | Calgary | AB | Leduc | AB
1 | CFF | FGL | 8 LBS | 321 | CDC | Calgary | AB | Edmonton | AB |  |  |  | As you can see, based on main file, I created 2 file. Overland 8 LBs weight & Overland 6 Lbs Weight and same for Rates too.
1 | CFF | FGL | 8 LBS | 300 | CDC | Calgary | AB | Edmonton | AB
1 | CFF | FGL | 8 LBS | 600 | CDC | Calgary | AB | [NAME] | AB
2 | Gardewine | FGL | 8 LBS | 1369 | MDC | Mississauga | ON | Timmins | ON
2 | Gardewine | FGL | 8 LBS | 1369 | MDC | Mississauga | ON | Timmins | ON |  |  |  | I want to run single formula, so that [NAME] itself can find the appropriate rate in Column "J"  based on,  "Rates" file
2 | Gardewine | FGL | 8 LBS | 1138 | MDC | Mississauga | ON | Sudbury | ON
2 | [NAME] | FGL | 8 LBS | 1956 | MDC | Mississauga | ON | Pembroke | ON |  |  |  | Columns to look for in "Rates" file.
2 | Gardewine | FGL | 8 LBS | 2249 | MDC | Mississauga | ON | Sudbury | ON
2 | Gardewine | FGL | 8 LBS | 1894 | MDC | Mississauga | ON | Thunder Bay | ON |  |  |  | 1 | Column B | Carrier
2 | Gardewine | FGL | 8 LBS | 2125 | MDC | Mississauga | ON | Thunder Bay | ON |  |  |  | 2 | Column D | Weight Density
2 | Gardewine | FGL | 8 LBS | 360 | MDC | Mississauga | ON | Thunder Bay | ON |  |  |  | 3 | Column G | Origin City
2 | Gardewine | FGL | 8 LBS | 1538 | MDC | Mississauga | ON | Sault St. Marie | ON |  |  |  | 4 | Column H | Destination City
3 | Overland | FGL | 8 LBS | 51 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 711 | CDC | Calgary | AB | [NAME] | BC |  |  |  | and return the "Freight Rate" result on Column J
3 | Overland | FGL | 8 LBS | 70 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 308 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 1449 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 367 | CDC | Calgary | AB | Penticton | BC
3 | Overland | FGL | 8 LBS | 1413 | CDC | Calgary | AB | Penticton | BC
3 | Overland | FGL | 8 LBS | 990 | CDC | Calgary | AB | Richmond | BC
3 | Overland | FGL | 8 LBS | 183 | CDC | Calgary | AB | Burnaby | BC
3 | Overland | FGL | 8 LBS | 711 | CDC | Calgary | AB | Vernon | BC
3 | Overland | FGL | 8 LBS | 1000 | CDC | Calgary | AB | Vernon | BC
3 | Overland | FGL | 8 LBS | 119 | CDC | Calgary | AB | Vernon | BC
3 | Overland | FGL | 8 LBS | 737 | CDC | Calgary | AB | Victoria | BC
3 | Overland | FGL | 8 LBS | 163 | CDC | Calgary | AB | Victoria | BC
3 | Overland | FGL | 8 LBS | 30 | CDC | Calgary | AB | Kamloops | BC
3 | Overland | FGL | 8 LBS | 628 | CDC | Calgary | AB | Kamloops | BC
3 | Overland | FGL | 8 LBS | 684 | CDC | Calgary | AB | Vancouver | BC
3 | Overland | FGL | 8 LBS | 455 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 468 | CDC | Calgary | AB | Victoria | BC
3 | Overland | FGL | 8 LBS | 1360 | CDC | Calgary | AB | Victoria | BC
3 | Overland | FGL | 8 LBS | 755 | CDC | Calgary | AB | Victoria | BC
3 | Overland | FGL | 8 LBS | 1305 | CDC | Calgary | AB | Vancouver | BC
3 | Overland | FGL | 8 LBS | 457 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 561 | CDC | Calgary | AB | Chilliwack | BC
3 | Overland | FGL | 8 LBS | 1386 | CDC | Calgary | AB | Chilliwack | BC
3 | Overland | FGL | 8 LBS | 728 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 183 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 732 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 1408 | CDC | Calgary | AB | Langley | BC
3 | Overland | FGL | 8 LBS | 120 | CDC | Calgary | AB | Langley | BC
3 | Overland | FGL | 8 LBS | 244 | CDC | Calgary | AB | Courtenay | BC
3 | Overland | FGL | 8 LBS | 274 | CDC | Calgary | AB | Cranbrook | BC
3 | Overland | FGL | 8 LBS | 250 | CDC | Calgary | AB | Terrace | BC
3 | Overland | FGL | 8 LBS | 711 | CDC | Calgary | AB | Terrace | BC
3 | Overland | FGL | 8 LBS | 737 | CDC | Calgary | AB | Terrace | BC
3 | Overland | FGL | 8 LBS | 500 | CDC | Calgary | AB | Terrace | BC
3 | Overland | FGL | 8 LBS | 728 | CDC | Calgary | AB | Terrace | BC
3 | Overland | FGL | 8 LBS | 316 | CDC | Calgary | AB | Langford | BC
3 | Overland | FGL | 8 LBS | 478 | CDC | Calgary | AB | Langford | BC
3 | Overland | FGL | 8 LBS | 2187 | CDC | Calgary | AB | Langford | BC
3 | Overland | FGL | 8 LBS | 2231 | CDC | Calgary | AB | Langford | BC
3 | Overland | FGL | 8 LBS | 180 | CDC | Calgary | AB | Smithers | BC
3 | Overland | FGL | 8 LBS | 693 | CDC | Calgary | AB | Smithers | BC
3 | Overland | FGL | 8 LBS | 600 | CDC | Calgary | AB | Tsawwassen | BC
3 | Overland | FGL | 8 LBS | 600 | CDC | Calgary | AB | Tsawwassen | BC
3 | Overland | FGL | 8 LBS | 300 | CDC | Calgary | AB | Tsawwassen | BC
3 | Overland | FGL | 8 LBS | 600 | CDC | Calgary | AB | Tsawwassen | BC
3 | Overland | FGL | 8 LBS | 300 | CDC | Calgary | AB | Tsawwassen | BC
3 | Overland | FGL | 8 LBS | 24 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 24 | CDC | Calgary | AB | Kamloops | BC
3 | Overland | FGL | 8 LBS | 38 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 675 | CDC | Calgary | AB | [NAME] | BC
3 | Overland | FGL | 8 LBS | 77 | CDC | Calgary | AB | West Vancouver | BC
4 | [NAME] | FGL | 8 LBS | 700 | CDC | Calgary | AB | North Battleford | SK
4 | [NAME] | FGL | 8 LBS | 729 | CDC | Calgary | AB | North Battleford | SK
4 | [NAME] | FGL | 8 LBS | 747 | CDC | Calgary | AB | Estevan | SK
4 | [NAME] | FGL | 8 LBS | 325 | CDC | Calgary | AB | Estevan | SK
4 | [NAME] | FGL | 8 LBS | 268 | CDC | Calgary | AB | Estevan | SK
4 | [NAME] | FGL | 8 LBS | 1605 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 105 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 2898 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 1423 | CDC | Calgary | AB | [NAME] | SK
4 | [NAME] | FGL | 8 LBS | 339 | CDC | Calgary | AB | [NAME] | SK
4 | [NAME] | FGL | 8 LBS | 425 | CDC | Calgary | AB | Saskatoon | SK
4 | [NAME] | FGL | 8 LBS | 694 | CDC | Calgary | AB | Saskatoon | SK
4 | [NAME] | FGL | 8 LBS | 2880 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 1432 | CDC | Calgary | AB | [NAME] | MB
4 | [NAME] | FGL | 8 LBS | 809 | CDC | Calgary | AB | [NAME] | MB
4 | [NAME] | FGL | 8 LBS | 1245 | CDC | Calgary | AB | [NAME] | MB
4 | [NAME] | FGL | 8 LBS | 490 | CDC | Calgary | AB | [NAME] | MB
4 | [NAME] | FGL | 8 LBS | 68 | CDC | Calgary | AB | [NAME] | MB
4 | CFF | FGL | 8 LBS | 729 | CDC | Calgary | AB | Swift Current | SK
4 | CFF | FGL | 8 LBS | 430 | CDC | Calgary | AB | Swift Current | SK
4 | [NAME] | FGL | 8 LBS | 703 | CDC | Calgary | AB | Selkirk | MB
4 | [NAME] | FGL | 8 LBS | 667 | CDC | Calgary | AB | Selkirk | MB
4 | [NAME] | FGL | 8 LBS | 300 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 300 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 300 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 385 | CDC | Calgary | AB | Winnipeg | MB
4 | [NAME] | FGL | 8 LBS | 85 | CDC | Calgary | AB | Saskatoon | SK
5 | Speedy | FGL | 8 LBS | 703 | MDC | Mississauga | ON | Woodstock | ON
5 | Speedy | FGL | 8 LBS | 685 | MDC | Mississauga | ON | Midland | ON
5 | Speedy | FGL | 8 LBS | 711.1 | MDC | Mississauga | ON | Cornwall | ON
5 | Speedy | FGL | 8 LBS | 2880 | MDC | Mississauga | ON | [NAME] | ON
5 | Speedy | FGL | 8 LBS | 666.7 | MDC | Mississauga | ON | Pembroke | ON
5 | Speedy | FGL | 8 LBS | 2196 | MDC | Mississauga | ON | Ottawa | ON
5 | Speedy | FGL | 8 LBS | 712 | MDC | Mississauga | ON | Ottawa | ON
5 | Speedy | FGL | 8 LBS | 596 | MDC | Mississauga | ON | [NAME] | ON
5 | Speedy | FGL | 8 LBS | 658 | MDC | Mississauga | ON | Orangeville | ON
5 | Speedy | FGL | 8 LBS | 711.1 | MDC | Mississauga | ON | Brockville | ON
5 | Speedy | FGL | 8 LBS | 300 | MDC | Mississauga | ON | Whitby | ON
6 | CFF | MARKS | 6 LBS | 406 | MDC | Mississauga | ON | Sussex | NB
6 | CFF | MARKS | 6 LBS | 453 | MDC | Mississauga | ON | [NAME] | NS
6 | CFF | MARKS | 6 LBS | 207 | MDC | Mississauga | ON | [NAME] | NS
6 | CFF | MARKS | 6 LBS | 420 | MDC | Mississauga | ON | Halifax | NS
6 | CFF | MARKS | 6 LBS | 466 | MDC | Mississauga | ON | New Glasgow | NS
6 | CFF | MARKS | 6 LBS | 1565 | MDC | Mississauga | ON | Truro | NS
6 | CFF | MARKS | 6 LBS | 1839 | MDC | Mississauga | ON | Fredericton | NB
6 | CFF | MARKS | 6 LBS | 500 | MDC | Mississauga | ON | Yarmouth | NS
6 | CFF | MARKS | 6 LBS | 533 | MDC | Mississauga | ON | Yarmouth | NS
6 | CFF | MARKS | 6 LBS | 1086 | MDC | Mississauga | ON | Corner Brook | NL
6 | CFF | MARKS | 6 LBS | 819 | MDC | Mississauga | ON | Antigonish | NS
6 | CFF | MARKS | 6 LBS | 926 | MDC | Mississauga | ON | Gander | NL
6 | CFF | MARKS | 6 LBS | 520 | MDC | Mississauga | ON | Gander | NL
6 | CFF | MARKS | 6 LBS | 473 | MDC | Mississauga | ON | Gander | NL
6 | CFF | MARKS | 6 LBS | 433 | MDC | Mississauga | ON | [NAME] | NS
6 | CFF | MARKS | 6 LBS | 959 | MDC | Mississauga | ON | Carbonear | NL
6 | CFF | MARKS | 6 LBS | 520 | MDC | Mississauga | ON | Bathurst | NB
6 | CFF | MARKS | 6 LBS | 560 | MDC | Mississauga | ON | Amherst | NS
6 | CFF | MARKS | 6 LBS | 1046 | MDC | Mississauga | ON | Fredericton | NB
6 | CFF | MARKS | 6 LBS | 533 | MDC | Mississauga | ON | Saint John | NB
6 | CFF | MARKS | 6 LBS | 980 | MDC | Mississauga | ON | Summerside | PEI
6 | CFF | MARKS | 6 LBS | 446 | MDC | Mississauga | ON | Tracadie-Sheila | NB
6 | CFF | MARKS | 6 LBS | 500 | MDC | Mississauga | ON | Tracadie-Sheila | NB
6 | CFF | MARKS | 6 LBS | 752 | MDC | Mississauga | ON | [NAME] | NL
6 | CFF | MARKS | 6 LBS | 1918 | MDC | Mississauga | ON | St. John's | NL
6 | CFF | MARKS | 6 LBS | 2112 | MDC | Mississauga | ON | St. John's | NL
6 | CFF | MARKS | 6 LBS | 480 | MDC | Mississauga | ON | St. John's | NL
6 | CFF | MARKS | 6 LBS | 2232 | MDC | Mississauga | ON | St. John's | NL
6 | CFF | MARKS | 6 LBS | 2085 | MDC | Mississauga | ON | Mount Pearl | NL
6 | CFF | MARKS | 6 LBS | 466 | MDC | Mississauga | ON | Mount Pearl | NL
6 | CFF | MARKS | 6 LBS | 1066 | MDC | Mississauga | ON | Atholville | NB
7 | Gardewine | MARKS | 6 LBS | 580 | MDC | Mississauga | ON | New Liskeard | ON
7 | Gardewine | MARKS | 6 LBS | 553 | MDC | Mississauga | ON | [NAME] | ON
7 | Gardewine | MARKS | 6 LBS | 413 | MDC | Mississauga | ON | Fort Frances | ON
7 | Gardewine | MARKS | 6 LBS | 386 | MDC | Mississauga | ON | Fort Frances | ON
7 | Gardewine | MARKS | 6 LBS | 320 | MDC | Mississauga | ON | Kapuskasing | ON
7 | Gardewine | MARKS | 6 LBS | 393 | MDC | Mississauga | ON | Dryden | ON
8 | Overland | MARKS | 6 LBS | 1074 | CDC | Calgary | AB | Yellowknife | BC
8 | Overland | MARKS | 6 LBS | 533.3 | CDC | Calgary | AB | Prince [NAME] | BC
8 | Overland | MARKS | 6 LBS | 533.3 | CDC | Calgary | AB | Prince [NAME] | BC
9 | [NAME] | MARKS | 6 LBS | 939 | MDC | Mississauga | ON | Bracebridge | ON
10 | [NAME] | MARKS | 6 LBS | 554 | CDC | Calgary | AB | Portage La Prairie | MB
10 | [NAME] | MARKS | 6 LBS | 395 | CDC | Calgary | AB | Winkler | MB
10 | [NAME] | MARKS | 6 LBS | 815 | CDC | Calgary | AB | Swift Current | SK
10 | [NAME] | MARKS | 6 LBS | 780 | CDC | Calgary | AB | [NAME] | SK
11 | Speedy | MARKS | 6 LBS | 1652 | MDC | Mississauga | ON | [NAME] | ON
11 | Speedy | MARKS | 6 LBS | 1012 | MDC | Mississauga | ON | Collingwood | ON
11 | Speedy | MARKS | 6 LBS | 892 | MDC | Mississauga | ON | Richmond Hill | ON
11 | Speedy | MARKS | 6 LBS | 879 | MDC | Mississauga | ON | Wasaga Beach | ON
11 | Speedy | MARKS | 6 LBS | 446 | MDC | Mississauga | ON | Uxbridge | ON
12 | VTL | MARKS | 6 LBS | 999 | MDC | Mississauga | ON | St-Constant | QC
12 | VTL | MARKS | 6 LBS | 920 | MDC | Mississauga | ON | Quebec City | QC
12 | VTL | MARKS | 6 LBS | 1346 | MDC | Mississauga | ON | Quebec | QC
12 | VTL | MARKS | 6 LBS | 952 | MDC | Mississauga | ON | Levis | QC
12 | VTL | MARKS | 6 LBS | 553 | MDC | Mississauga | ON | Granby | QC
12 | VTL | MARKS | 6 LBS | 473 | MDC | Mississauga | ON | Shawinigan | QC
12 | VTL | MARKS | 6 LBS | 493 | MDC | Mississauga | ON | Saint-Georges | QC
12 | VTL | MARKS | 6 LBS | 2045 | MDC | Mississauga | ON | Greenfield Park | QC
12 | VTL | MARKS | 6 LBS | 1973 | MDC | Mississauga | ON | St-Bruno de Montarville | QC
12 | VTL | MARKS | 6 LBS | 1911 | MDC | Mississauga | ON | Boucherville | QC
12 | VTL | MARKS | 6 LBS | 1513 | MDC | Mississauga | ON | Brossard | QC
12 | VTL | MARKS | 6 LBS | 546 | MDC | Mississauga | ON | Saint-Hyacinthe | QC
13 | Speedy | FGL | 8 LBS | 12798 | MDC | Mississauga | ON | Gatineau | QC
13 | Speedy | FGL | 8 LBS | 3569 | MDC | Mississauga | ON | Gatineau | QC