Comparing two lists with cell values partially different

[Zain001]

Hello all,

First time posting here.

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.

Excel example.JPG

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_Name Compare.xlsx

[JohnTopley]

removed by Jt

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...erent.1223387/)

[Zain001]

Thank you for bringing this to my attention. My apologies for this.

[tanasedn]

With Power query:

Please Login or Register  to view this content.

[Glenn Kennedy]

tanasedn. You attached the wrong file. There are no results in it!!

[tanasedn]

Hi, Glen!
Thank's, i'm still sleeping!
Changes made!

[shukla.ankur281190]

Try

D23

Please Login or Register  to view this content.