Comparing two columns sounds like a five-second job until the lists have different lengths, trailing spaces, duplicates or values that look identical but are stored as different types. The method you choose depends on whether you need a quick visual check, a filtered list of differences, or a reusable formula.
These four methods cover the common cases. They all work without uploading the workbook.
1. COUNTIF: the fastest match/difference test
Put the first list in column A and the second in column B. In C2, test whether each value in A appears in B:
=COUNTIF($B$2:$B$1000, A2)>0
TRUE means the value exists in both lists. For values only in A, reverse the test:
=COUNTIF($B$2:$B$1000, A2)=0
This is case-insensitive and ignores text vs number formatting in many cases, which is usually what you want for a quick reconciliation. If case matters, use EXACT or a SUMPRODUCT comparison instead.
2. FILTER: return only the differences
In Microsoft 365, FILTER turns the TRUE/FALSE column into a clean list:
=FILTER(A2:A1000, COUNTIF($B$2:$B$1000, A2:A1000)=0, "No differences")
This spills every value that appears in A but not in B. Swap the ranges to get the values only in B. The third argument handles the case where the lists match perfectly, so the cell does not show a confusing #CALC!.
3. XLOOKUP: show the matching value or a missing flag
=XLOOKUP(A2, $B$2:$B$1000, $B$2:$B$1000, "Missing")
This returns the value from B when it finds a match and “Missing” when it does not. It is easy to read and works well in a report table. For a case-sensitive comparison, use EXACT inside SUMPRODUCT or a helper column.
4. Conditional formatting: see the differences in place
- Select A2:A1000.
- Home → Conditional Formatting → New Rule → Use a formula.
- Enter
=COUNTIF($B$2:$B$1000,$A2)=0. - Choose a fill colour and click OK.
Every value that is missing from B is highlighted in place. This is the best method for a visual audit or a screenshot in a report, but it does not produce a list you can sort or export.
Duplicates inside one list
A value can appear in both lists and still be a duplicate. Count occurrences as you go down:
=COUNTIF($A$2:$A2, A2)>1
The expanding range $A$2:$A2 marks the second and later occurrences. To count duplicates across both lists, combine them with VSTACK in 365 or copy them into one column and use the same formula.
The four traps that produce false differences
- Trailing spaces: “Alpha ” is not “Alpha”. Wrap both sides in
TRIM, or clean the source first. - Numbers stored as text: 1001 and “1001” can look identical. Check the alignment or use
VALUE. - Case differences: COUNTIF is case-insensitive. If the brief requires exact case, use
=EXACT(A2,B2)or SUMPRODUCT with EXACT. - Duplicate rows: COUNTIF tells you whether a value exists, not how many times. Add a count column if multiplicity matters.
A five-minute cleanup checklist
- Trim both columns and remove blank rows.
- Convert numbers and dates to the correct type.
- Decide whether the comparison is case-sensitive.
- Count duplicates before you call the lists “matched”.
- Keep a column showing the method so the result is auditable.
Do it in the browser, without uploading the file
Paste both lists into the free Compare Lists tool to get matches, values only in A, values only in B, and duplicates. It runs entirely in your browser, lets you ignore case and spaces, and exports a CSV you can attach to your report. The tool also shows the COUNTIF, FILTER and EXACT formulas so you can reproduce the result in Excel.
For the conditional-counting logic behind these formulas, read the COUNTIF and SUMIFS guide. If the lists came from a messy export, the Data Cleaner removes duplicates, spaces and line breaks first.
Need the reconciliation built and explained for an assignment? Send the workbook to an Excel expert for a fixed quote.
