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)