Text lookup within a string of data

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have a very challenging excel task at hand. I have basically two sets of data (names of employees). One set it a master list of all employees, while the other set is a list of email responses from those employees in the master list that have responded to me. The exact structure of each name is not identical, which basically throws a VLOOKUP out of consideration. However, the general set up is the same (Last name, First name).

I am trying to devise a formula that will take each of the cells which contains name of each the person in the master list, and then do some sort of text search to see if any cells contain that assortment of letters. One of the lists has a space after the comma (Doe, John), while the other list does not (Doe,John). Another challenge is the list of email responses was copied directly out of Outlook, so it may contain their informaiton as well (Doe, John (SAL) vs. Doe,John).

Basically, I need to have my response column have a "Y" populate if the person did respond, and then I need to have it blank if they did not respond (meaning the name was not found in the employee response data set).

Example.

1. Master

A1 Smith,John
A2 Doe,John
A3 St. John,Mike

2. Response

A1 Smith, John (Finance)
A2 Doe, John
A3 St John,Mike P


As you can see, since minor changes exist in some cases, it makes this a very diffcult forumula to construct with a VLOOKUP, which doesn't handle minor variations in text very well.

If anyone can help me on this I would be so grateful! Please let me know if you want me to clarify anything.

Thanks,
Eric

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
A perfect match is not possible. By perfect match I mean each name cannot be accounted for. Based on data and looking at various patterns you can perhaps reduce it down to few names that you have to manually check off.
You can do lookup for perfect match. If vlookup fails you can try to various sort of matches. A major issue that I see is lack of sufficient data to see various possible ways name can show up