📎 This article includes 1 downloadable practice file ↓
Bank statement vs ledger, this month’s customers vs last month’s, the GST portal vs your purchase register — comparing two lists is one of the most common reconciliation jobs. Excel can tell you what matches, what’s missing on each side, and highlight the differences.
In this article
Is this item in the other list?
List 1 in A2:A200, list 2 in C2:C150. Next to list 1:
=IF(COUNTIF($C$2:$C$150, A2) > 0, "In both", "Only in list 1")
=IF(ISNUMBER(MATCH(A2, $C$2:$C$150, 0)), "In both", "Missing")COUNTIF is easy to read; MATCH is a little faster on big lists and tells you where the match is.
Excel 365: the whole difference list in one formula
=FILTER(A2:A200, COUNTIF(C2:C150, A2:A200) = 0, "Nothing missing") ' in list 1, not in list 2
=FILTER(C2:C150, COUNTIF(A2:A200, C2:C150) = 0, "Nothing new") ' in list 2, not in list 1
=FILTER(A2:A200, COUNTIF(C2:C150, A2:A200) > 0) ' in bothWrap with UNIQUE() if a list has repeats, and SORT() to tidy the output.
Compare on two columns (invoice number + GSTIN)
=IF(COUNTIFS(Portal!$A:$A, A2, Portal!$C:$C, C2) > 0, "Matched", "Not in portal")Matching on one column alone gives false matches when two suppliers reuse the same invoice number.
Highlight differences with conditional formatting
Select A2:A200, Home › Conditional Formatting › New Rule › Use a formula:
=COUNTIF($C$2:$C$150, A2) = 0Choose a fill colour. Items missing from list 2 light up and update automatically as data changes.
Compare two columns row by row
=IF(EXACT(A2, B2), "Same", "Different") ' case-sensitive
=IF(A2 = B2, "Same", "Different") ' ignores caseWhere people go wrong
| Symptom | Cause | Fix |
|---|---|---|
| Obvious matches reported as missing | Trailing spaces (“C-104 ”) | Compare TRIM(A2), or clean both lists first |
| Numbers never match | One list has numbers, the other text numbers | Convert with VALUE or --, or compare A2&"" |
| Partial names match wrongly | Wildcards in data (* or ?) inside COUNTIF | Use MATCH/XMATCH exact, or escape with ~ |
| Slow workbook | COUNTIF on whole columns × thousands of rows | Limit ranges or use Tables |
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.