Searching Impartial Values from one List on another

[KawanMSC]

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	Email
James Cleveland Owens
Walter Elias Disney

List 2:

Name	email
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!

[#2]

@KawanMSC 

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

[#3]

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!

[#4]

@KawanMSC 

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?

[#5]

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,

[#6 · Accepted solution]

@KawanMSC 

You 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.

[#7]

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

[#8]

@KawanMSC , you are welcome, glad to help