matching names from one Excel Spreadsheet to another

Question

I have two separate spreadsheets, both with several hundreds of employees names. My task at the moment is to go through one of the spreadsheets to see if any employees are matching on both, which will at the same time show me the employees that are not.

There are only names in the cells, no numbers. I was thinking of trying to do a vlookup, but as the only data I am looking for is names, I am thinking that this is not necessary?

So, Column a has first names, and columns B has surnames in both of the spreadsheets.

What would be the best way to do an accurate match lookup? Is there a way of cross referencing a name and then finding out which cell is the match from the other spreadsheet?

(FYI - I have tried this formula =IF(ISNA(MATCH(M2,Sheet2!M:M,0)),"Not found","Found") - However after manually checking a couple of names for accuracy, the formula seems to be showing an incorrect answer?

Thanks so much!

Answer 1

Good Day,

What you might try is creating an extra column in both worksheets and using the concatenate formula to add the first and last names together.

You could then use a conditional formatting to highlight names on both worksheets. The link below will provides a good video on how to use conditional formatting against multiple worksheets. You can then filter by colour to see the list of duplicates.

https://www.youtube.com/watch?v=VhECzNIQTIY

The one thing you should use a duplicate conditional formatting to insure there aren't duplicate names on the individual spreadsheets first.

I hope that helps, Brad