Compare Two Excel Lists: Missing and Matching Values

What exactly are you comparing?

Comparing two Excel lists can mean three different tasks: find values present only in List A, find values present only in List B, or find values shared by both. Duplicates can also matter. First decide whether your comparison is based on unique IDs, repeated rows, or full records.

Suppose your first column contains AB-101, AB-102 and AB-105, while your second list contains AB-102, AB-105 and AB-109. The value only in A is AB-101; only in B is AB-109; both share AB-102 and AB-105.

Method 1: use COUNTIF directly in Excel

If List A is in cells A2:A100 and List B is in B2:B100, enter this formula in C2 and copy it down:

=IF(COUNTIF($B$2:$B$100,A2)=0,"Only in A","In both")

The result marks each value from column A according to whether it occurs anywhere in B. To find values only in B, reverse the references:

=IF(COUNTIF($A$2:$A$100,B2)=0,"Only in B","In both")

Depending on your Excel locale, argument separators may be semicolons rather than commas. You can use the Excel formula separator converter, then verify the formula in your installation. COUNTIF is generally not case-sensitive and counts occurrences, so it is not a substitute for an exact, case-sensitive or multi-column comparison.

Method 2: compare pasted lists without formulas

1. Open the free Excel list comparison tool.
2. Paste the values from List A, one per line.
3. Insert a line containing exactly three hyphens: ---.
4. Paste the values from List B below that separator.
5. Run the comparison and inspect which values belong only to the first list, only to the second, and to both.

AB-101
AB-102
AB-105
---
AB-102
AB-105
AB-109

This is useful for comparing ID exports without creating helper columns. The text is processed in the browser tab, not sent to a conversion server. Check the tool options for normalization and duplicate handling before depending on the result.

Why two apparently identical values may not match

Leading or trailing spaces

AB-102 and AB-102 are not the same text. Apply a consistent trimming rule to both exports before comparing them.

Zeros at the beginning of an ID

Excel may interpret 00123 as the number 123 when importing a CSV. If leading zeros are meaningful, import the ID column as text and verify sample rows.

Uppercase, lowercase, and hidden characters

Some comparisons ignore case while others do not. Non-breaking spaces and invisible Unicode characters can also produce surprising results. Compare a few known records manually before applying a result at scale.

Duplicate IDs and composite keys

A set comparison may ignore the number of times a value appears. If you need to compare transactions by multiple fields (such as ID, currency and date), create a reliable composite key or use Power Query and review the records that don't match.

When to automate the workflow

For recurring exports with changing columns, multiple sheets, or thousands of records, a repeatable Power Query workflow or tested VBA macro may be more reliable than repeatedly pasting two lists. Describe the input format, key fields and expected report in a request for an Excel automation quote. The inquiry itself is free.

For data-quality preparation, see the CSV deduplication tool and Excel text cleanup tool.