Calculating Shipment Rates Base on Origin City and Destination City

**Chnatko** · 04-04-2014, 07:20 PM

Hey, what I am trying to figure out is how to match a series of rates for a destination city depending upon the origin city. I have figured out how to match rates from origin city to multiple destination cities but have not figured out how to change the series of rates when the origin city changes. These rates will be calculated on the "calculator" in the excel document depending on the cities chosen.

I have attached a document that shows a simplified version of what I am trying to do.

Any help would be greatly appreciated. Thanks!

**FDibbins** · 04-04-2014, 08:20 PM

How many different rate tables do you have, and how would you know which 1 to use? Looking at the calc sheet, I cannot see where it tells me to use "Vancouver? or "Edmonton?

Maybe create a range name for each table. It looks like the city names are the same in both (+other???) tables, so you could probably use only 1 set of names, and then change the forumla to something like this...
=INDEX(Vancouver,MATCH(E28,Rates!A1:A39,0),MATCH(A28,Rates!B1:G1,1))

If you put the table name in its own cell (and maybe use data valudation), you could change to something like this. (A25 contains my DV list)...
=INDEX(INDIRECT(A25),MATCH(E28,Rates!A2:A39,0),MATCH(A28,Rates!B1:G1,1))

**Chnatko** · 04-09-2014, 11:11 AM

Currently there is only one rate table being used the "From Vancouver To" table. The rates being calculated right now only incorporate the Destination Province and City Delivering Point tables, which are matching to the rates on the "From Vancouver to" table. In other words when I am choosing a destination city and province the freight is pulling these numbers from the "From Vancouver To" Table. =INDEX(Rates!A1:G39,MATCH(E28,Rates!A1:A39,0),MATCH(A28,Rates!A1:G1,1)) So as you can see in the formula currently being used its only matching E28 to the corresponding city on the From Vancouver To rate table.

I have individual rate tables for each city to the rest of the cities, for instance, From Calgary To every city like in the Vancouver rate table.

So what I want to do is be able to choose the Origin city, which is based on the origin province (Just using data validation here) and choose a destination city based on the destination province. Once those are chosen the corresponding "From (City) To" rate table will be match to the Origin city and the freight charge will match to the destination city on this rate table as well as the freight weight, finding the correct weight.

For example:

Freight Weight: 800, Origin Province: Alberta, Origin City: Edmonton, Destination Province: Ontario, Destination City: Toronto, Freight Charge:2

Hopefully that is more clear, sorry for not explaining it as well before hand.