Are letter case and extra spaces taken into account?

No: before comparison the key is stripped of extra spaces and converted to a single case, and the letter É is treated as E.

What if a key is repeated?

Repeats are flagged; the comparison uses the first match found in the other list.

Will it match “Smith J. R.” and “Smith John Robert”?

No, the key is matched exactly after cleaning; there is no fuzzy matching.

← All templates

Compare Two Lists in Excel — Excel Template

Match two lists by a key: what is in both, what is in only one, and where values differ. Ignores spaces, letter case and accents.

Free

Download free

Blank template + filled-in example · Excel 2007+, LibreOffice, Google Sheets

What it is for

Reconciling two exports: staff lists from different systems, payments and invoices, contract lists, registers. Paste the lists and Excel shows which records are in both, which are in only one, and where the key matches but the values differ.

What is inside

Sheets “List A” and “List B”: a key and two additional values, up to 5,000 rows each.
Automatic key cleaning: extra spaces, letter case, and the letter É (treated as E).
A result for every row: “In the other list”, “Only here”, “Found, but values differ”; repeated keys are flagged.
Sheet “Summary”: total, matched, only in A, only in B, mismatches, duplicates.
Sheets “Only in A”, “Only in B” and “Mismatches” with ready-made lists and row numbers.

Typical uses

A common task is to reconcile two lists: HR records against payroll, a supplier list against your own register, or a list received from head office. The template finds mismatches and duplicates in seconds instead of a manual check.

Who it is for

Accountants, HR specialists, auditors and anyone who reconciles lists by hand with VLOOKUP and filters.

Compatibility

Excel 2007 and later, LibreOffice. No macros or external links. The file is in English.

Questions and answers

How many rows can I compare?: Up to 5,000 rows in each list.
Are letter case and extra spaces taken into account?: No: before comparison the key is stripped of extra spaces and converted to a single case, and the letter É is treated as E.
What if a key is repeated?: Repeats are flagged; the comparison uses the first match found in the other list.
Will it match “Smith J. R.” and “Smith John Robert”?: No, the key is matched exactly after cleaning; there is no fuzzy matching.