# published fare

#### hanasamo

##### Board Regular
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try this, it's a bit cumbersome but it works:
Book1
ABCD
1RouteFare
2pek-sha1130
3pek-sha-pek2260
4sha-pek1130
5can-sha900
Sheet1

Formula in B2 is :
Code:
``=IF(LEN(A2)=7,IF(ISNA(VLOOKUP(A2,Fares,2,0)),VLOOKUP(RIGHT(A2,3)&"-"&LEFT(A2,3),Fares,2,0),VLOOKUP(A2,Fares,2,0)),2*(IF(ISNA(VLOOKUP(LEFT(A2,7),Fares,2,0)),VLOOKUP(RIGHT(A2,7),Fares,2,0),VLOOKUP(LEFT(A2,7),Fares,2,0))))``
This assumes that :
(1) your Published Fares are in a named range "Fares" in this workbook
(2) city codes are always 3 digits
(3) routes with more than 2 city codes are RETURN flights not multiple destinations.

HTH

Thanks Fergus.

Your advice is not quite understandable to me, I don't know if your function can work when there is a route like"pek-sha-can-sha-pek".

No, the formula would not work with routes like "pek-sha-can-sha-pek". If you wanted a formula to do that, especially for an unlimited number of sectors it would be very long and cumbersome.

For what you want I reckon you need a macro, which is not my field, lets hope one of the code-gurus sees this and comes up with a solution for you.

ADVERTISEMENT
Thank you so much. I'll expect a VBA expert who can help me.

I'm waiting.......

ADVERTISEMENT
Assuming that you have each leg listed (e.g. pek-sha and sha-pek):

I have placed your legs in column G and the prices in H.

In A1, I put "pek-sha-van"
In cell B1:
=SUM(IF(ISNUMBER(SEARCH(\$G\$1:\$G\$4,A2)),\$H\$1:\$H\$4,0))
Enter with shift-ctrl-enter.

tactps said:
Assuming that you have each leg listed (e.g. pek-sha and sha-pek):

I have placed your legs in column G and the prices in H.

In A1, I put "pek-sha-van"
In cell B1:
=SUM(IF(ISNUMBER(SEARCH(\$G\$1:\$G\$4,A2)),\$H\$1:\$H\$4,0))
Enter with shift-ctrl-enter.

Thank you, your function worked partially, for those multiple legs routes, it seems that it doesn't sum all the legs but part of them.

=SUM(IF(ISNUMBER(SEARCH(\$G\$1:\$G\$4,A2)),\$H\$1:\$H\$4,0))

First the search is the key:

It looks for the first leg (e.g. "pek-sha") as a string in cell A2. If it exists it then looks at the price (H1:H4), otherwise it ignores it (0).
Then it goes ont o the next one and add them up at the end.

That's clever tactps, but note that for return legs, i.e. sha-pek as opposed to pek-sha the route and fare have to be separately entered in columns G and H.

Replies
3
Views
528
Replies
4
Views
671
Replies
8
Views
4K
Replies
5
Views
641
Replies
6
Views
933

Threads
1,196,268
Messages
6,014,344
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back