Compare two lists in Excel
Find matched occurrences and items left over in each list. Repeated items are counted individually, so two copies of an ID cannot be matched against a single copy.
Paste one item per line
Limit per list: 500 nonblank items and 100,000 UTF-8 bytes. Empty lines are ignored. A line containing only spaces is an item unless you select Ignore outer spaces. Quoted CSV is not parsed: each line is one literal item.
Default: case-sensitive, whitespace significant. Each occurrence can match once. Results display the original text. Optional case matching uses JavaScript lowercase, without Unicode normalization or language-specific folding.
Your comparison
Input line numbers include ignored empty lines. Matched pairs follow List A order; each consumes the earliest remaining match in List B. Only-A and only-B items keep their own source order. Each table starts with up to 100 rows. Use Show all rows below a table to inspect the rest.
Matched pairs
Only in A
Only in B
Reproduce occurrence matching in Excel
Use text IDs in A2:A6 and B2:B6. Put x, x, y, Z, 001 in column A and x, y, y, z, 001 in column B. The browser example produces three matched pairs, two only-A items (the second x and Z), and two only-B items (the second y and z). Matching is case-sensitive.
In C2, label the occurrence in A. Fill this formula down to C6:
=IF(A2="","",IF(SUMPRODUCT(--EXACT(A2,$A$2:A2))<=SUMPRODUCT(--EXACT(A2,$B$2:$B$6)),"Matched","Only A"))In D2, label the occurrence in B, then fill down to D6:
=IF(B2="","",IF(SUMPRODUCT(--EXACT(B2,$B$2:B2))<=SUMPRODUCT(--EXACT(B2,$A$2:$A$6)),"Matched","Only B"))The growing range counts this item's occurrences so far; the fixed range counts how many exist in the other list. A blank cell stays blank. Space-only text is a value. Format identifiers as Text before entering 001 so its zeroes survive. These formulas reproduce the default exact-text mode; Excel TRIM is not identical to the browser's optional trim.
Version and locale
The recipe uses EXACT for case-sensitive text comparison and SUMPRODUCT to count matches. These functions are documented for Microsoft 365 on Windows and Mac and Excel 2016–2024 where listed by Microsoft. The recipe was not exercised in Excel for the web. No dynamic array function or Ctrl+Shift+Enter is required. Formulas here use English names and commas; your Excel locale may use translated function names and semicolons. This is a reproducible recipe, not a claim of native Excel execution.
For other tasks, browse tutorials and Excel tips.