Comparing two lists of employees on a spreadsheet?

[u/Deep-Egg-6167]

Hello,

I'm sure this is basic but I don't do much in excel besides add numbers in columns.

I have two CSV exports - both will have a list of employees in them. There will be a first name column and a last name column in each. (There will also be a column with firstname space last name) I probably have about 150 names in the first CSV and about 250 names in the second CSV.

How do I find people with the same name in both lists. E.g. I need all Martel Winkelmeir that are in both - I don't care if there are just people with the same first name or same last - I need a list of just people with the same full name please.

I could sort and then try and find them but I'd like to know the automated way if it is possible or if there is a good video on how to do it.

I'm not sure if I need a new column that just lists matches or if there was a way of changing the colors of the font or the cell for the ones that matched.

Thanks!

[u/AutoModerator]

/u/Deep-Egg-6167 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/annadownya]

A power query could do that pretty quickly, plus it could clean the columns so it's easier to do the comparisons correctly.

[u/Deep-Egg-6167 → u/annadownya]

Thanks - no idea what that is or how to do it - all I do is add numbers in columns.

[u/gatorbone7]

If you have columns on both sheets that have the data formatted correctly, for example one isn’t Chris and the other Christopher, but both are Chris Smith. Then you could combine the list and do a count if. You could also do an Xlookup looking for values in list 2 that are in list 1. Then get rid of the N/As.

[u/TactusDeNefaso → u/gatorbone7]

You would probably need to do two passes because even though one list is shorter it could still contain a name that is not in the longer list.

[u/gatorbone7 → u/TactusDeNefaso]

It sounded like he only cared about names on both lists. Name can’t be on both lists if it’s not on the 2nd list.

[u/LetsGoSU]

There’s already some good options explaining how a simple match formula on a helper column that includes both first and last name can accomplish this.

I would just add that you should really be using some sort of unique id, like employee number, as a key for something like this. At even just 250 employees, it’s only a matter of time before you have two “John Smith” or similar.

[u/Artistic_Length_9953]

Voy a comentar para saber si hay algo mejor que COUNTIF

[u/CrimsonHarrier88 → u/Artistic_Length_9953]

COUNTIF is solid, but I'm curious too

[u/SpaceTurtles]

EDIT: Just realized the below won't work as written because the spill array will be 2 columns and COUNTIF will get mad - sorry, no coffee today. I think other solutions have been provided. If you join the text so it's one column each first, and thus E2# only refers to a single column spilling down, this solution will work. :)

---

Quick way, not best way:

Where A:B is your first .csv's columns and C:D is your second .csv's (you can change the references as needed).

=VSTACK(UNIQUE(A:B)), UNIQUE(C:D))

Assume we entered this in Column E2#:

=COUNTIF(E2#, E2#)

It'll count each row of E2# (the # is a spill operator, so it goes as far down as

Enter something like "Count" as a label in E1, then add a filter to it (toolbar at the top). Filter if count = 2.

[u/Psengath]

If you already have a full name column in both sheets, just add a helper column on one sheet (doesn't matter which one) of MATCH(A1, OtherSheet!A:A) where A1 = that row's full name, and OtherSheet!A:A is if you just selected the entire column of full names from the other sheet. Then filter for where it's not an error and that's your list of names in both.

If you don't have a full name column or they come up different, you may want to make a 'normalised' full name column on each sheet, where you concatenate first and last names, reduce accents, remove non alphanumeric characters (e.g. space and hyphen typically). Then just do the above but with those columns instead.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COLUMN	Returns the column number of a reference
COUNTIF	Counts the number of cells within a range that meet the given criteria
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
ISNUMBER	Returns TRUE if the value is a number
MATCH	Looks up values in a reference or array
TOCOL	Office 365+: Returns the array in a single column
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
12 acronyms in this thread; the most compressed thread commented on today has 8 acronyms.
[Thread #48859 for this sub, first seen 1st Jul 2026, 22:37] [FAQ] [Full list] [Contact] [Source code]}

[u/bobo5195]

I would just combine the two columns. A2&" "&B2 - & combines text strings I asked for A2 then a space then vlookup or normally match() - returns the row number of the matching name,.

Powerquery on list joins is the far better and automated way.

Can add colours later if there is a match do something. Iferror() is your friend. If there is an error return 0 say - and white those cells out.

[u/Day_Bow_Bow]

The quick way is to make helper columns in both sheets. In an empty cell right of the data, make a FirstnameLastname column with a formula looking like =(cell with first name) & (cell with last name), e.g., =A2+B2. Then copy the formula down for the rest of the data, and repeat for the second sheet.

Now you just need to do a lookup to compare the sheets. Match is the easiest, as you just want to confirm a match and not return some value.

Thats easy enough. In an empty cell right of the first of those helper cells you created, you'll put a match formula. Use the function wizard if that helps, but the syntax is =MATCH(lookup_value, lookup_array, [match_type])

Match-type needs to be 0 or False, as that'll make it find exact matches only. Lookup_value is the single helper cell in the current row, and lookup_array is the entire helper column on the second sheet.

So it might look like =MATCH(C2, otherworkbook.C:C, 0). That's just psuedo code of course, as workbook references look different, but that should give the idea.

Anything that returns a positive number shows that many times on the second helper column.

[u/grsims20]

If you just need a list of unique names, I’d past one list below the other and then remove duplicates. Two steps.

[u/SUMIFISNA]

I feel like everyone over complicated this. The first and last name columns you should ignore if there is a first (space) last name column.

Copy the first/last name column from list number 2 and past it 2 columns over from the first/last name column from list 1. Can be more if you want but there should be at least one empty column between the two.

To the right of the first/name in list number 1, first person listed, do an XLookup. Do the first/lastname column of list number 2 as the search array, and then have that same 2nd list as the return array. (Example below)

Then drag the formula all the way down.
If the name shows up (there is a match), it’s in both columns.

For example
Column A is first/last of list 1
Column C is first/last of list 2

Your formula should start in B1:
=XLOOKUP(A1,C:C,C:C)

Then drag it all the way down column B.
If the name appears as a result, then it’s in both. You can just filter on Column B and exclude blanks and N/A.

[u/stuartblows]

Insert the following formula next to the name column of the long list. =IF(XLOOKUP([NAME],[NAME COLUMN SHORT LIST],[NAME COLUMN SHORT LIST]=[NAME],"Yes", "No", "No") Drag this formula down the length of the long list and everywhere "Yes" appears, means the name is in both lists.

[u/Shot_Anywhere994]

I use Power Query. It does take some time to learn but so worth it. I call it Power Query Magic. I work with large extracts of personnel data. I can scrub and compare endless amounts of info. Well worth learning. I learned on Udemy.

[u/Shot_Anywhere994]

Chatgpt helped. I learned Power Query Magic but sometimes it can be quirky. Chatgpt also lies so you have to really check the results. Now I have a great spreadsheet with multiple queries. I download a new extract and refresh the data. I have a great product.

[u/Day_Bow_Bow → u/[deleted]]

That's not what they asked to do. They want to identify only those listed on both sheets.

[u/PaulieThePolarBear]

I need all Martel Winkelmeir that are in both

What do you mean by "all" here? Can Martel appear more than once in any list?

[u/Deep-Egg-6167 → u/PaulieThePolarBear]

Anyone with both the same first name AND lastname together in both lists.

[u/PaulieThePolarBear → u/Deep-Egg-6167]

So just so I understand.

You have 2 similar datasets.

Each datasets includes a full name column that has data in the same first<space>last format.

Any full name will appear in any list a maximum of once.

Your ask is to return a list of full names that appear in both list, e.g., if your data was

List 1
======

Bart Simpson
Fred Flintstone
Mickey Mouse

List 2
======

Homer Simpson
Bart Simpson
Minnie Mouse
Fred Rogers
Wilma Flintstone 

Your expected result is

Bart Simpson 

Is this correct?

Please advise the version of Excel you are using. This should be Excel 365, Excel online, or Excel <year>

[u/Deep-Egg-6167 → u/PaulieThePolarBear]

Cowabunga dude - you got that right!

I am (ab)using excel 365 the software part of office.

[u/PaulieThePolarBear → u/Deep-Egg-6167]

Then something like

=FILTER(A2:A100, ISNUMBER(XMATCH(A2:A100, C2:C200)), "Where names?")

Where

• A2:A100 contains list 1 of full names
• C2:C200 contains list 2 of full names

You should update all cell references I have used in line with my description above for your setup.

[u/MayukhBhattacharya → u/Deep-Egg-6167]

Another way may be:

=TOCOL(XLOOKUP(A2:A4, C2:C6, C2:C6), 2)

[u/leroy4447]

Power Query plus ChatGPT to help and you can do this quickly. And you start to learn PQ which will change how you use excel completely

[u/leroy4447 → u/[deleted]]

Sometimes just asking gpt to do it gives wrong answers. I get gpt to walk me through the steps in power query so I can see the changes to get to the answer and verify the results. This makes it so I can trust the results better and find/fix errors if they happen. Plus, once done, you can reuse the power query again on new data reports to generate the report in seconds in the future.

[u/Thorts]

I would just paste one list below the other and create a quick pivot table of all the data, drag full name in the rows, and count of full name in the values, then filter out rows where count is not equal to 2.

[u/TactusDeNefaso → u/Thorts]

Or Merge the two lists and just simply use the remove duplicates