Apr 14 2020 10:05 AM
Hello Good people.
Here is what has been keeping me up at night: I have two lists of names, one with full names and other with partial names and related email address. Example:
List 1:
Name | |
James Cleveland Owens | |
Walter Elias Disney |
List 2:
Name | |
James Owens | James.owens@company.com |
Walter Disney | walter.disney@company.com |
What I need to do is populate the Email column on list one with the emails on list 2. I need excel to identify Jesse Cleveland Owens and Jesse Owens as the same to give me the corresponding email. I tried to use vlookup with TRUE for an approximate match, but since my actual database has several "James", the returned value ends up being the name and email of a different James.
Anyone could help me out here?
Best to all, and stay safe!
Apr 14 2020 10:11 AM
Could you please clarify names in first table are ALWAYS have 3 parts, or that could be something like
Walter Elias Disney Jr
Walter Elias Disney Sr
Joe Doe
Apr 14 2020 10:17 AM
Hello @Sergei Baklan ,
We're talking Brazilian names here actually, so they vary between 3 parters, 4 parters or even 5 parters. But the combination on List 2 is always unique.
Hope this helps, and thank fro such a quick response.
Best!
Apr 14 2020 10:26 AM
Thank you. But perhaps there is some other logic, e.g. first and last part are to be taken to compare with second list, like
Walter Elias Mark Disney => Walter Disney and never Walter Mark
Or there is no common logic at all?
Apr 14 2020 10:31 AM
Hello @Sergei Baklan ,
There is no common logic, because we could have two people named
Walter Elias Mark Disney => Walter Disney (First + Last)
Walter Bob Patrick Disney => Walter Patrick (First + Third)
That happens because many people will have similar last and middle names here and the company has over a thousand employees, so you can see how there would be lots of these similarities.
I realize this may end up being a case where I will need to check by hand, but I want to exhaust my options.
best,
Apr 14 2020 10:52 AM
SolutionYou may try something like this
In C3
=IFERROR(
INDEX($G$3:$G$9,
MATCH(1,
INDEX(ISNUMBER(SEARCH($E$3:$E$9,B3))*
ISNUMBER(SEARCH($F$3:$F$9,B3)),0
),0
)
),"no such")
and drag it down.
Please check in attached file.
Apr 14 2020 02:15 PM
Hello @Sergei Baklan
Sir, you are amazing. Thank you so **bleep** much for this it was of great help.
Keep up the great work!
Best
Apr 15 2020 12:37 AM
@KawanMSC , you are welcome, glad to help
Apr 14 2020 10:52 AM
SolutionYou may try something like this
In C3
=IFERROR(
INDEX($G$3:$G$9,
MATCH(1,
INDEX(ISNUMBER(SEARCH($E$3:$E$9,B3))*
ISNUMBER(SEARCH($F$3:$F$9,B3)),0
),0
)
),"no such")
and drag it down.
Please check in attached file.