Finding specific text using two lists

[Joe Shaer]

Hello -

I have two lists:
Column A is the 'City Name'
Column B is the 'address' which may or may not contain a city name found in Column A.

I would like to search through Column B to find any City Name matching the list in Column A. Is this possible?

I am providing a screenshot sample of the results I am looking for.

Thank you

 

[Gargamelbarbosa]

 

[Joe Shaer]

View attachment 80594

Awesome! Thank you so much!

 

[Joe Shaer]

Awesome! Thank you so much!

Is it possible you may explain why the '2' and '1/SEARCH' is used in LOOKUP function?

 

[Gargamelbarbosa]

Hi,
I know why we use that but my English is not enough explain. "2" is not important, just it must be greater than 1.

 

[bosco_yip]

Hi,

Please see explanation by Aladin Akyurek of Mr Excel Forum as in this link Level #09 :

Lookup for a value in Unsorted data | MrExcel Message Board

Remark : The 1st person to use this formula is Aladin Akyurek in August 2004 with explanation as in the link.

Regards

 

[shrivallabha]

Hi,

Please see explanation by Aladin Akyurek of Mr Excel Forum as in this link Level #09 :

Lookup for a value in Unsorted data | MrExcel Message Board

I was about to post this link.

Simple thing to note in this case is: the construct SEARCH produces a series of Numbers(greater than or equal to one) and Errors (#VALUE!)) which in turn gets converted to numbers less than 1 or #VALUE which get looked up by 2!