published fare

[hanasamo]

I have a air passenger tickets price list in a xls file called "published fare", it's like:
pek-sha 1130
pek-can 1500
sha-can 900
.
.
.
.
.
.
And there is a column in another xls file contains city pair like that:
pek-sha
pek-sha-pek
sha-pek
can-sha
.
.
.
.
.
.
I want to use a macro or a function that can list the price for each route in the column next to the city pair column, as listed in the "published fare",pek-sha & sha-pek are both 1130, pek-sha-pek=1130+1130=2260,etc. how can I do this?

Thanks in advance

 

[tactps]

Thanks Fergus - Yes, you are correct:

sha-pek and pek-sha are two different things per the formula.

But hey, how long do we need thje formula to be? lol

So for:
"pek-sha-van"
you would need the legs:
pek-sha
and
sha-van

For pek-sha-van-sha-pek
you would need
pek-sha
sha-van
van-sha
sha-pek