ε€€VALUES

🧾 Comparing two name lists in Excel

Sign-ups against payments, last year against this year β€” finding who is missing and who is on both. Here is how with plain Excel.

1 β€” The fastest way: one COUNTIF line

Next to list A, put this in an empty cell and drag it down:

=IF(COUNTIF(B:B, A2)>0, "found", "missing")

A2 is the name to check; B:B is the column holding the other list. Rows that say missing are people not on the other list.

Do it the other way round too. Looking from one side tells you half the story β€” β€œin A but not in B” and β€œin B but not in A” are different people.

To see only those rows: select the column β†’ Data β†’ Filter β†’ tick only β€œmissing”.

2 β€” If you just want to see it in colour

Put both lists side by side β†’ select both name columns β†’ Home β†’ Conditional Formatting β†’ Highlight Cells Rules β†’ Duplicate Values

Shared names light up. Quick β€” but it does not count them for you.

3 β€” Why it usually says β€œno match” anyway

To your eye they are the same name; to Excel they are not. It is nearly always one of these four:

CauseExample
An invisible spaceJohn Smith  with one space at the end β€” the most common
Spacing inside the nameMary-Jo ↔ Mary Jo ↔ MaryJo
Brackets or titles attachedJohn Smith (Year 3) ↔ John Smith Β· Dr. Lee ↔ Lee
Numbers stored as textStudent ID 20250001 is a number on one side and text on the other

Clean first

=TRIM(A2) removes spaces at the ends =SUBSTITUTE(TRIM(A2), " ", "") removes the spaces in the middle too =LOWER(...) ignores capital letters

Compare the cleaned columns and far more will match. Then turn the cleaned results into values (Copy β†’ Paste Special β†’ Values), or sorting will scramble them.

4 β€” What is left: things only a person can tell

From here a person has to look. That is why a good tool does not quietly merge things β€” it puts the look-alikes side by side so you can choose.

If you do this every week

We sell a small Windows program: give it two lists and it separates who is missing and who is on both. It does the cleaning above for you, and anything that still does not match is shown side by side β€” β€œare these the same person?” β€” so you decide. It never merges on its own.

No internet needed, nothing leaves your computer. A monthly subscription you can cancel any time.

πŸ“‹ Smart List Compare β€” $1.99 / month

Where this comes from

Formulas work in any recent version of Excel; in some regions Excel uses ; instead of , between arguments.
Everyone’s situation is different. The numbers here are examples β€” swap in your own.

VALUES is a small shop that makes tools to take work off your desk Β· More guides Β· The tool shelf Β· Bookshop Β· Free downloads Β· Seller information