lookup multiple values(strings) in a single cell with col A & B(diff sheet) & return col B

HI Guys,

I can't find a solution to my problem after browsing through numerous posts and threads. Kindly help me out

In the sheet attached, i want to get the "Ranking" in column B of sheet 1 by looking up city (col A sheet 1) with col a and B of sheet 2 and return value in col B(ranking)

Its easy to do a vlookup when the cell contains one value for eg: "Houston" can be looked up from sheet 2

But some of my cells contain: Houston;Miami;Seattle in one cell and i want to get the value of the city which has the highest rank among the three.

Is this possible? Let me know if you need more clarity on this. I have attached the excel file with the post.
Thanks in advance

Or try this ...

=AGGREGATE(14,6,Sheet2!$B$2:$B$16/(SEARCH(Sheet2!$A$2:$A$16,$A2)>0),1)

Normal enter.

WOwww...both solutions worked perfectly fine !! Thanks a ton you guys for your quick responses.

@Phuocam - Could you breakdown your formula so that i can understand this for future use? i didn't understand why you used 14,6 in the beginning.
Greatly appreciate all your help

Also if i want to give a default ranking value to the unmatched string(which wasn't in sheet 2) how can i do this?

I cannot get the formula working on my actual file. Guys any clue why? i have attached the sheet with this post.
I need to get the score of the cities in col I and display it in col Q.
Lookup tab is "City Vlookup"
For the blank values i need to return a default value 80
For the unmatched cells i need to return a default value 80.

Try ...

=IF(I2="",80,IFERROR(AGGREGATE(14,6,'City Vlookup'!$B$2:$B$31/(SEARCH('City Vlookup'!$A$2:$A$31,I2)>0),1),80))

Pure Genius Phuocam !! That worked. Could you please please break down this formula and expain in 1/2 lines(or as a syntax). I am really eager to learn this part:
"IFERROR(AGGREGATE(14,6,'City Vlookup'!$B$2:$B$31/(SEARCH('City Vlookup'!$A$2:$A$31,I2)>0),1)"
many thanks!!