Keyword Match Vlookup

[teek0001]

I have a huge list of locations that are written in a variety of formats (city/country, region/country, city/state/country, etc.). I want to just pull the country out of the cell and disregard the other information. I have a master list of every country in a separate sheet and was wondering if there was an easy way to create a vlookup that would only spit out the country name when it is mentioned in the list of locations. Hope that makes sense

 

[Sheltonite]

Is the country always the last listed item? And are the values always separated by "/"?

 

[teek0001]

Is the country always the last listed item? And are the values always separated by "/"?

The country is usually the last listed item. They are not separated by a slash[TABLE="width: 178"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10KM SW of Pointe Claire, Montreal, Canada[/TD]
[/TR]
</tbody>[/TABLE]

The above is an example of a cell.

 

[Aladin Akyurek]

Let's try:

=LOOKUP(9.99999999999999E+307,(SEARCH(" "&CountryList&" "," "&A2&" "),CountryList)

where A2 houses a string like above.

 

[Sheltonite]

If I may ask a question... I tried recreating this scenario and the formula above works great for when the country is at the end of the string. However, when I got curious and decided to pick this apart and tried just the search() formula to see what results, it's returning #VALUE!. Is this simply because it is referring to the named range instead of a single cell and the larger formula is able to handle that? If you have a moment, an explanation of how this worked would be awesome and greatly appreciated.

 

[GrilledCheese]

One easy way is to enter your country by itself on a new column. and then create the vlookup.

 

[Aladin Akyurek]

If I may ask a question... I tried recreating this scenario and the formula above works great for when the country is at the end of the string.

It should also work with country anywhere in the string.

However, when I got curious and decided to pick this apart and tried just the search() formula to see what results, it's returning #VALUE!. Is this simply because it is referring to the named range instead of a single cell and the larger formula is able to handle that? If you have a moment, an explanation of how this worked would be awesome and greatly appreciated.

All countries in the (named) range are searched in the target string (in A2) all at once. The following link contains an explanation:

http://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3567172

Hope this helps.