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]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Something like this?

23 05 08.xlsm
	A	B
1	Column 1: Text String	Column 2: Person's Name
2	Text string containing John Citizen. No further text.	John Citizen
3	Text string containing Bill Jones. No further text.	Bill Jones
4	Text string containing Jane Smith. No further text.	(not found)
5	Text string containing Michael King. No further text.	Michael King
6
7
8	List of Names
9	Bill Jones
10	Michael King
11	John Citizen
Names

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(LOOKUP(9^9,SEARCH(A$9:A$11,A2),A$9:A$11),"(not found)")

 

[pschroeder]

Perfect! Thank-you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Please don't forget the first paragraph of my previous post though.

 

[Tom.Jones]

Hi,

You can use Flash Fill

 

[pschroeder]

Profile updated.
Would you mind elaborating on the logic behind the syntax "Lookup(9^9"please?

 

[Peter_SSs]

You can use Flash Fill

Could you elaborate on how? Flash fill gives me Jane Smith for row 4, not "not found" as requested by the OP.

Would you mind elaborating on the logic behind the syntax "Lookup(9^9"please?

9^9 is a quick and easy way to write a big number. At least big enough for this situation - see below.
If we look at the B5 formula in my mini sheet. It is

=IFNA(LOOKUP(9^9,SEARCH(A$9:A$11,A5),A$9:A$11),"(not found)")
Once the blue part is evaluated the formula looks like this
=IFNA(LOOKUP(387420489,{#VALUE!;24;#VALUE!},A$9:A$11),"(not found)")

When LOOKUP cannot find the big number being looked for, it uses the last number in the array that it can find (24) and returns the value from the corresponding position (position 2) of the A9:A11 array. The number 24 is not in itself relevant, only its position in the array.
Hope that makes sense.

BTW, thank you for updating your details.

 

[pschroeder]

Thanks Peter.
Although you've said the number 24 itself is irrelevant but its position in the array is important relative to the corresponding value being verified/retrieved in the array, I'm not seeing where the number 24 corresponds to anything at all in the array. The array resides within rows 9-11, so it doesn't appear that the number 24 corresponds to a row number.
How is the formula resolving the number 24 and what is it corresponding to in the array? It's not quite clear to me...... would you mind elaborating further please? (just trying to better understand the underlying logic)
Thanks.

 

[Peter_SSs]

Sorry if this is basically repeating what I said above but I will try to express it in a slightly different way
The value 24 is not relevant. LOOKUP cannot find 9^9 in the array produced by the formula as shown in my previous post: {#VALUE!;24;#VALUE!}
Since it cannot find 9^9 it looks for the last number that it can find and determines where in that array the last number is found
The last (only) number found in that array happens to be 24 and it occupies position 2 in the array.
LOOKUP therefore returns the value from position 2 in the range A9:A11

Another example perhaps
=LOOKUP(9^9,{"abc";28;#DIV/0!;3;"x6yz"},{8;280;6;404;600})
would return 404 because, with 9^9 not appearing in the blue array, LOOKUP determines that the last number in that blue array (3) occupies position 4 in the blue array and so returns the value from position 4 of the red array.

 

[pschroeder]

Thank-you.