0

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)

3
  • The fourth term for vlookup is TRUE/FALSE with TRUE being 'Approximate match' which I have not experimented with too much. You could also look for the first two terms from sheet1 separated by spaces appearing in sheet 2 with an AND() statement to capture most matches.
    – Brian
    Jun 11, 2019 at 18:26
  • Yeah I've tried using TRUE instead of FALSE in the VLOOKUP and it ends up flagging a bunch of people who don't actually match since it's just looking at bits and pieces of their names. Can you elaborate a bit more on the full syntax of the AND statement? I can't picture what you mean.
    – EJF
    Jun 11, 2019 at 18:37
  • Well I was hoping to use vlookup to find the partial names as you did, but it appears not to be so. My idea was something like this, which is not working. if(and(vlookup(fname),vlookup(lname),"yes","")
    – Brian
    Jun 11, 2019 at 19:53

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.