Comparing two lists with cell values partially different

[Zain001]

Hello all,

I need to compare two lists. In List A I need to find people who are present in list B. But the problem is that the names in list B are not always complete. I need to use a formula to minimize the time spent.

Detail:

List A contains all the employees. This list contains the full names of employees. If there's a middle name, or if there are two last names, it'll be present in this one.

List B Contains only the people whose bonuses are approved. But in this list, the names are simplified, as in, the middle name or the second last name might not be present.

The task is to populate List A with the data from List B. If a person from List A is present in List B, we should get "Bonus Approved" as result. If a person from List A is not present in List B, then there could be an error, an empty cell, or a text like "Bonus not approved", this is not very important, but nice to have.

I tried VLOOKUP, but obviously, the results are not complete as the values in the "Name" columns is not always matching exactly.

How can modify the formula, or use another one to make sure that even if a name is partially present in list B, the results are shown in List A?

Example file: Internet Share - Google Drive

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Comparing two lists with cell values partially different
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[shinigamilight]

=IFERROR(IFERROR(INDEX($H$4:$H$11,MATCH(C4,$G$4:$G$11,0),1),VLOOKUP(TRIM(REPLACE(C4,FIND(" ",C4),FIND("@",SUBSTITUTE(C4," ","@",2))-FIND(" ",C4),"")),$G$4:$H$11,2,0)),VLOOKUP(LEFT(C4,3)&"*",$G$4:$H$11,2,0))

fix the spelling of nzarrio in source data

 

[Zain001]

=IFERROR(IFERROR(INDEX($H$4:$H$11,MATCH(C4,$G$4:$G$11,0),1),VLOOKUP(TRIM(REPLACE(C4,FIND(" ",C4),FIND("@",SUBSTITUTE(C4," ","@",2))-FIND(" ",C4),"")),$G$4:$H$11,2,0)),VLOOKUP(LEFT(C4,3)&"*",$G$4:$H$11,2,0))

fix the spelling of nzarrio in source data

Thanks a lot!
In my real-life file, this really narrows down the list. There will still be some human checks needed, as the lists are not well kept.
But this solves the main issue!

Thanks for your time and effort!