validate a character string within a cell to a list and return the value found

[pschroeder]

I have a text string containing a person's name and need to lookup that person's name in a separate list of names to verify that the person exists in the list. I then need to then return the person's name when found in the list. For example:-

Column 1: Text String​	Column 2: Person's Name (the required formula)​	Comments​
Text string containing John Citizen. No further text.	John Citizen	<--- Formula which returns the person's name from the "List of Names" when a match is found in the "List of Names" for the highlighted text.
Text string containing Bill Jones. No further text.	Bill Jones
Text string containing Jane Smith. No further text.	(not found)
Text string containing Michael King. No further text.	Michael King

Using the first record as the example.......
The name John Citizen exists within the string "Text string containing John Citizen. No further text." Lookup the table "List of Names" and see if "John Citizen" is in the list. If "John Citizen" is in the List of Names, populate the formula cell with the name found in the List of Names i.e. return the value "John Citizen".

"List of Names" table looks like this:-

List of Names​
Bill Jones
Michael King
John Citizen

Note. The name "Jane Smith" is not listed in the "List of Names". The required formula needs to accommodate instances where the name contained within the string is not found in the "List of Names".

Would appreciate your help in the providing a formula (for column-2) I could use to achieve the above please.

Thanks.

 

[Peter_SSs]

No problem.