To compare two lists in Excel and find the differences, use COUNTIF against the other list: =IF(COUNTIF(ListB!A:A, A2) > 0, "Match", "Not in Vendor List") flags every row as present or absent. Conditional formatting shows the same result visually, and INDEX/MATCH pulls details across for the rows that do match. Wrap values in TRIM(CLEAN()) first if either list came from an export, as trailing spaces cause false mismatches.
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.
Example: vendor statement against internal records
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.
What the COUNTIF flag returns
| 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 its column B, such as the vendor’s invoice amount. The sample lists above carry only account numbers, so give List B a second column to try it. 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.
Common questions
Is COUNTIF case sensitive?
No. “ACME” and “acme” are treated as a match. For a case-sensitive comparison use =SUMPRODUCT(--EXACT(A2, ListB!A:A))>0 instead.
Why is every row flagged as missing?
Usually trailing spaces on one list, or numbers stored as text on one side and as real numbers on the other. Wrap both in TRIM and check with ISNUMBER.
Can I compare more than two lists?
Yes. Add one COUNTIF column per list, then read the pattern across the row to see which lists each value appears in.
Most mismatches are dirty data, not missing records
When two lists refuse to match, the cause is usually formatting rather than content: trailing spaces, inconsistent case, numbers stored as text, or the same company written three ways. COUNTIF sees those as genuine differences.
Excel Data Cleaner cleans one list at a time. Run each list through it before you compare, and the emails, names, phone numbers and dates in both lists come out formatted the same way, so a comparison reflects real differences rather than formatting noise. Free up to 100 records per file, and it runs entirely on your own PC.
Headed for a CRM? Add-on exporters turn the cleaned list into an import-ready file for HubSpot, Salesforce, Zoho, Pipedrive or monday.
See Excel Data CleanerWindows PC required: it runs inside Excel on Windows, not on a phone, a tablet or a Mac. Why it does not run on Mac.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.