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

pschroeder

New Member
Joined
Mar 6, 2012
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
AB
1Column 1: Text StringColumn 2: Person's Name
2Text string containing John Citizen. No further text.John Citizen
3Text string containing Bill Jones. No further text.Bill Jones
4Text string containing Jane Smith. No further text.(not found)
5Text string containing Michael King. No further text.Michael King
6
7
8List of Names
9Bill Jones
10Michael King
11John Citizen
Names
Cell Formulas
RangeFormula
B2:B5B2=IFNA(LOOKUP(9^9,SEARCH(A$9:A$11,A2),A$9:A$11),"(not found)")
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)

Please don't forget the first paragraph of my previous post though. ;)
 
Upvote 0
Profile updated.
Would you mind elaborating on the logic behind the syntax "Lookup(9^9"please?
 
Upvote 0
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. :confused:

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. (y)
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top