KHIZHNYAKOVHow to Compare Two Lists in Excel
Compare Two Lists in Excel — Excel Template — Download free →
Comparing two lists is one of the most common jobs in Excel and one of the most tedious when done by eye. Two exports of employees, an invoice list against a payment list, your supplier register against the one from head office: the task is always the same. For each row, is it in the other list, and if so, do the details agree?
This guide shows three reliable formula methods, how to clean your data so they work, and what to watch out for.
Start with clean data
Most failed comparisons fail because of invisible differences. "Smith " with a trailing space is not "Smith". "ACME" and "Acme" look different to some functions. Before comparing, create a helper column with a cleaned key:
=LOWER(TRIM(A2))
TRIM removes extra spaces and LOWER makes everything lower case. If your data contains accents, decide whether you want "Jose" and "José" to match; Excel will not do it for you, so replace accented letters with SUBSTITUTE or compare on a code instead. A numeric ID is always better than a name when you have one.
Method 1: COUNTIF, the quick check
Put the lists on two sheets. In list A, next to each key:
=IF(COUNTIF(ListB!$A:$A,A2)>0,"In both","Only in A")
Do the reverse on list B. In two minutes you know what is only in one list. COUNTIF also finds repeated keys: =COUNTIF(A:A,A2)>1 flags duplicates inside a single list.
Method 2: VLOOKUP or XLOOKUP, to compare values
Matching keys is half the job. Often you also need to see whether an amount or a date agrees. Suppose each list has a key in column A and an amount in column B:
=IFERROR(VLOOKUP(A2,ListB!A:B,2,FALSE),"Not found")
Put this in column C of list A, then in column D:
=IF(C2="Not found","Only in A",IF(C2=B2,"Match","Values differ"))
In Excel 365 the same with XLOOKUP is =XLOOKUP(A2,ListB!A:A,ListB!B:B,"Not found"). Older versions of Excel do not have XLOOKUP, so VLOOKUP is the safe choice.
Worked example
| Key | List A amount | List B amount | Result |
|---|---|---|---|
| INV-001 | 500 | 500 | Match |
| INV-002 | 320 | 302 | Values differ |
| INV-003 | 150 | (missing) | Only in A |
| INV-004 | (missing) | 75 | Only in B |
The second row is the dangerous one: a transposed digit that a quick visual check would likely miss. Filtering for anything that is not "Match" gives you a short list to investigate.
Method 3: Conditional formatting for a visual check
Select list A, choose Conditional Formatting, New Rule, Use a formula, and enter =COUNTIF(ListB!$A:$A,$A2)=0. Rows with no match turn a colour. It is fast for a one-off look, but it does not give you a list you can hand to someone, so use formulas for anything serious.
Common mistakes
- Comparing text that looks like numbers. "00123" and 123 are different; make both columns the same type.
- Ignoring duplicates. A lookup returns the first match, so a repeated key can hide a discrepancy.
- Expecting fuzzy matches. "Smith J. R." and "Smith John Robert" will not match; use an ID or standardize names first.
- Whole-column formulas on huge lists. They slow down; limit ranges if you have tens of thousands of rows.
Ready-made template: compare two lists
If this is a regular job, use the free template. It works in Excel 2007 and later and in LibreOffice, with no macros or external links.
- Sheets List A and List B: a key and two additional values, up to 5,000 rows each.
- Automatic key cleaning: extra spaces, letter case and the letter É (treated as E).
- A result for every row: In the other list, Only here, or Found, but values differ. Repeated keys are flagged.
- Summary: total, matched, only in A, only in B, mismatches and duplicates.
- Only in A, Only in B and Mismatches: ready-made lists with row numbers.
Keys are matched exactly after cleaning, so there is no fuzzy matching. When a key repeats, the comparison uses the first match found in the other list.
FAQ
What is the fastest way to find rows in one list but not the other?
A COUNTIF helper column against the other list, then filter for zero.
How do I compare lists in different orders?
Lookup functions do not care about order. Only a row-by-row comparison does, which is why you should avoid it.
Can I compare more than two columns?
Yes. Combine columns into one key with =A2&"|"&B2, or compare each value column separately.
Why does my VLOOKUP return errors for values I can see?
Usually a trailing space, a text-versus-number mismatch or a hidden character. Clean the key with TRIM and make the types consistent.
