You have two lists — maybe a vendor’s invoice list and your internal records, or this month’s customer list and last month’s. You need to figure out what’s in both, what’s only in one, and what’s missing from the other. This comes up constantly in accounting, inventory, HR, and operations. Here are three ways to do it in Excel, from quick and simple to precise and flexible.
The Scenario
You have a list of account numbers from your system (List A) and a list from a vendor’s statement (List B). You need to find which accounts appear in both lists and which are in one but not the other.
List A — Your Records
| A | |
|---|---|
| 1 | Account # |
| 2 | ACC-1001 |
| 3 | ACC-1005 |
| 4 | ACC-1012 |
| 5 | ACC-1018 |
| 6 | ACC-1023 |
| 7 | ACC-1030 |
List B — Vendor Statement
| A | |
|---|---|
| 1 | Account # |
| 2 | ACC-1001 |
| 3 | ACC-1008 |
| 4 | ACC-1012 |
| 5 | ACC-1015 |
| 6 | ACC-1023 |
| 7 | ACC-1035 |
Method 1: COUNTIF — Quick Match/No Match Flag
The fastest way to check if each item in List A exists in List B. Add a helper column next to List A:
=IF(COUNTIF(ListB!A:A, A2) > 0, "Match", "Not in Vendor List")
COUNTIF counts how many times the value appears in List B. If it’s greater than 0, it’s a match.
Result
| A | B | |
|---|---|---|
| 1 | Account # | Status |
| 2 | ACC-1001 | Match |
| 3 | ACC-1005 | Not in Vendor List |
| 4 | ACC-1012 | Match |
| 5 | ACC-1018 | Not in Vendor List |
| 6 | ACC-1023 | Match |
| 7 | ACC-1030 | Not in Vendor List |
Do it both ways
Run the same formula on List B checking against List A. That way you find items missing from both sides — your records that the vendor doesn’t have, and vendor accounts you don’t have.
Method 2: Conditional Formatting — Visual Highlighting
If you just need a quick visual check without adding helper columns:
1Select your list (e.g., A2:A7 on List A)
2Go to Home → Conditional Formatting → New Rule
3Select “Use a formula to determine which cells to format”
4Enter the formula:
=COUNTIF(ListB!A:A, A2)=0
This highlights cells that do NOT appear in List B. Set the format to a red fill or red text.
Now any account number not found in the vendor’s list lights up in red instantly. You can scan the list visually without any helper columns cluttering your spreadsheet.
Method 3: INDEX/MATCH — Pull Details for Matches
When you don’t just need to know if it matches but also want to pull data from the matching record (like the amount, date, or status from the vendor’s list):
=IFERROR(INDEX(ListB!B:B, MATCH(A2, ListB!A:A, 0)), "No Match")
Finds the matching account in List B and returns the value from column B (e.g., the vendor’s invoice amount). Returns “No Match” if not found.
Watch for hidden mismatches
Two values can look identical but fail to match because of trailing spaces, non-breaking spaces, or different number formats. If your formulas return “No Match” for values you can see in both lists, use =TRIM(CLEAN(A2)) on both columns first to strip hidden characters.
Finding Duplicates Within a Single List
Sometimes you need to check for duplicates inside the same list before comparing across lists:
=IF(COUNTIF(A$2:A$100, A2) > 1, "Duplicate", "Unique")
Checks if the value in A2 appears more than once in the range. Flags duplicates so you can clean them before running your comparison.
When Formulas Aren’t Enough
Comparing two short lists with formulas is straightforward. But real business data makes it complicated fast:
- Lists with thousands of rows — formulas slow to a crawl and are hard to audit
- Inconsistent formatting — “ACC-1001” vs “ACC 1001” vs “acc1001” won’t match with COUNTIF
- Multiple comparison columns — matching on account number AND date AND amount simultaneously
- Recurring reconciliation — running the same comparison every week or month
- Output requirements — you need separate Match, Missing, and Duplicate sheets with formatted reports
- Multiple source files — comparing across 5, 10, or 50 files at once
A custom VBA reconciliation tool handles all of this — fuzzy matching, data cleanup, multi-column comparison, automated output to separate sheets, and one-click execution for recurring tasks.
“Extremely responsive, quick and reliable. Amazing skills.”
IT Consultation · Upwork
Drowning in Manual List Comparisons?
Tell us about your reconciliation challenge and we’ll show you how to automate it.
Start a Project