CompareOnline4U.

Home › Guides

How to compare two lists in Excel and find missing items

List Compare — open the free tool →

Comparing two lists is one of the most common spreadsheet jobs: which customers are missing from the mailing list, which order IDs are in the bank export but not in the accounting export, which products were dropped from the new catalogue. Here are four reliable ways to do it, from a no-formula option to Excel 365 dynamic arrays.

Method 1: Paste both lists into List Compare (no formulas)

  1. Open List Compare.
  2. Copy the first column in Excel and paste it into the List A box. Do the same for the second column in the List B box. Each cell becomes one line.
  3. Tick Ignore case if “ACME Ltd” and “Acme Ltd” should count as the same item.
  4. Click Compare now. Items only in List A are marked red, items only in List B green, and shared items stay unmarked.
  5. Use Copy report to get the three groups (only in A, only in B, in both) as text you can paste back into Excel.

Order does not matter, leading and trailing spaces are trimmed automatically and duplicates within a list are counted once. The lists are processed in your browser and not uploaded.

Method 2: COUNTIF (works in every Excel version)

Put List A in column A and List B in column B, both starting in row 2. In C2 enter:

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

Fill the formula down to the last item of List A, then filter column C for “Only in A”. To find items only in List B, repeat the idea in column D with =IF(COUNTIF($A:$A,B2)=0,"Only in B","In both").

Method 3: MATCH with ISNA

=ISNA(MATCH(A2,$B:$B,0)) returns TRUE when the value in A2 does not appear in column B. The last argument 0 forces an exact match; without it MATCH does an approximate match and gives wrong answers on unsorted lists.

Method 4: FILTER in Excel 365 or Excel 2021

Dynamic arrays can return the whole list of missing items in one formula:

=FILTER(A2:A500,COUNTIF(B2:B500,A2:A500)=0,"None")

The result spills down and updates automatically when either list changes. Swap the ranges to get the items only in List B.

What about Conditional Formatting?

Selecting both columns and choosing Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values colours values that appear more than once in the selection. It is quick for a visual check, but it also highlights repeats inside the same column, so it cannot tell you reliably which list an item is missing from.

Common reasons for wrong results

Need to compare whole rows rather than single values? Use Excel Compare or CSV Compare, which line up and compare complete rows.

List Compare — open the free tool →

Frequently asked questions

What is the easiest formula to find items in one list but not the other?

With List A in column A and List B in column B, put =COUNTIF($B:$B,A2)=0 next to the first item of List A and fill it down. TRUE means the item is missing from List B.

Is COUNTIF case-sensitive?

No. COUNTIF and MATCH treat “Apple” and “apple” as the same value. For a case-sensitive check use =SUMPRODUCT(--EXACT(A2,$B$2:$B$500))=0.

Why does Excel say an item is missing when I can see it in both lists?

Usually there is an extra space or an invisible character, or one value is stored as a number and the other as text. Clean both columns with TRIM (and VALUE or TEXT for numbers) before comparing.

Can I compare two lists without formulas?

Yes. Copy each column and paste it into List Compare. It shows items only in List A, only in List B and in both, ignores row order and runs in your browser.

More guides

How to compare two CSV files row by rowFind added, removed and changed rows between two CSV exports. Free method with tips on sorting, delimiters and columns for accurate results.How to compare two Excel files and find differencesCompare two Excel workbooks without VLOOKUP. Free online method, an Excel formula method, and tips to make rows line up correctly.How to find differences between two textsSpot every change between two versions of a text — essays, emails, contract clauses or notes. Free diff checker guide with tips to avoid false changes.How to compare two JSON files or API responsesDiff two JSON objects quickly: format minified JSON, line up keys and spot changed values. Free browser-based method for developers and testers.

← All comparison tools