Excel: comparing string values from different sheets that don't match exactly

Question

I have a workbook with two sheets, each of which contains a list of people. If a person from Sheet 1 also appears on Sheet 2, I need to be able to flag them on Sheet 1. I've added a column where I was going to insert a formula that returns some sort of value for anyone who's on Sheet 2.

This would be a simple task to accomplish by just using a VLOOKUP formula. But here's the catch: most of the names on Sheet 1 are shown in Last Name, First Name, Middle Initial format (some don't use a middle initial) whereas all the names on Sheet 2 only show Last Name, First Name (though I'm seeing a couple here and there with middle initials).

Example of what I'm wanting to see:

Sheet 1            In Sheet 2?       |       Sheet 2
-----------------------------------------------------------
Smith, John M.     YES               |       Smith, John
White, Mary S.                       |       Black, Phil
Green, Joe L.                        |       Doe, Jane T.
Doe, Jane T.       YES               |       Jones, Mike
Jones, Mike        YES               |       Adams, Ann
Brown, Bob C.      YES               |       Shaw, Frank
Gray, Carl R.                        |       Brown, Bob
Adams, Ann B.      YES               |       Hall, Tim

Is there a way to use the VLOOKUP function and not pull exact values? Obviously the names all match almost entirely and the middle initial is the only difference (that I've seen, anyway). Or would there be another more efficient way to do this?

(yes there's a risk of multiple people having the same name, but for now I'm ignoring that variable)

Answer 1

I'm answering my own question since I ended up figuring out a solution. As some additional background, Sheet 1 (titled "Main") contains a list of people who have visited an organization, with appropriate contact info to conduct satisfaction surveys about their visit. However, any visitors who are employees at that organization must be removed from the list in order to avoid biased survey responses. Sheet 2 (titled "Employees") contains a list of all the organization's current employees. Thus the idea was to compare the names on each list and flag any employees who appeared on the Main sheet. The Main sheet would have been the one using a middle initial for most visitors, while the Employees sheet was the one where most people didn't have a middle initial.

In a stroke of luck, I noticed that any employees who were listed as visitors were listed in the same format as on the Employee sheet: without a middle initial. Ergo their names matched perfectly on both sheets, and I'm not sure if this would have worked otherwise. I ended up using an INDEX MATCH function in a new column that pulled names from the Employee sheet and displayed them next to the corresponding visitor name on the Main sheet, thus showing me who to remove.

For reference, the name field is in column E on both sheets, and I ended up with the following formula, starting in Row 2 since Row 1 contained table headers.

=INDEX(Employees!E:E,MATCH(E2,Employees!E:E,0))

And this seems to have done the trick, at least for the purposes of this particular project. As I said before, it wouldn't have worked if the employee names weren't formatted the same on each sheet.