How to Compare Two Lists in Excel

To compare two lists in Excel, you check each item in one list against the other and flag the ones that are missing. COUNTIF does this in one short formula, FILTER pulls out every missing item at once, and conditional formatting highlights the gaps without any helper column. This guide shows all three on the same small example, plus how to compare lists row by row.

All the examples use an event sign-up list and an attendance list:

The question is simple: who registered but didn't come, and who came without registering?

Which method should you use?#

The right method depends on what you want to see. This table sums them up:

Compare two lists with COUNTIF#

COUNTIF counts how many times an item appears in the other list, so a result of 0 means the item is missing. In B2, type:

=IF(COUNTIF($C$2:$C$6,A2)>0,"Yes","No")

Copy it down to B7 (Windows: Ctrl+D; Mac: ⌘D). You get Yes for Ana, Ben, Dev and Ella, and No for Chloe and Femi. Those two registered but didn't attend.

The $ signs lock the attendance list, so every row checks the same range. A2 has no $, so it moves down to A3, A4 and so on. If you only want the count, =COUNTIF($C$2:$C$6,A2) on its own is fine: 1 means found, 0 means missing, and 2 or more means the name appears twice. The COUNTIF guide with criteria examples explains the function in depth.

To check the other direction, put the same formula next to the second list with the ranges swapped: =IF(COUNTIF($A$2:$A$7,C2)>0,"Yes","No"). Gus gets No, because he attended without registering.

Compare lists with XLOOKUP#

XLOOKUP is useful when you want to bring back the matching value or show a clear message. In B2:

=XLOOKUP(A2,$C$2:$C$6,$C$2:$C$6,"Missing")

Found names come back as themselves, and Chloe and Femi show Missing. The real power shows when the second list has more columns: change the return range to, say, a check-in time column, and you compare and fetch in one step. XLOOKUP needs Excel 2021 or Microsoft 365; see the XLOOKUP function guide for the syntax.

List the missing items with FILTER#

FILTER with COUNTIF gives you a clean list of everything missing, in one formula. In an empty cell, type:

=FILTER(A2:A7,COUNTIF(C2:C6,A2:A7)=0)

The result spills into two cells: Chloe and Femi. Here COUNTIF checks all six names at once and returns 0 for the missing ones, and FILTER keeps only those.

Swap the ranges to see the other side: =FILTER(C2:C6,COUNTIF(A2:A7,C2:C6)=0) returns Gus. To count how many registered people attended, use =SUMPRODUCT(--(COUNTIF(C2:C6,A2:A7)>0)), which returns 4. FILTER needs Excel 2021 or Microsoft 365. Make sure the cells below are empty, or you'll get a #SPILL! error.

Highlight differences with conditional formatting#

Conditional formatting colours the missing items, so you can see the differences without adding columns. Here's how:

1. Select A2:A7.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =COUNTIF($C$2:$C$6,A2)=0 and pick a fill colour.
5. Click OK.

Chloe and Femi turn coloured. Write the formula for the first cell of your selection, A2, and Excel adjusts it for the rest.

For a quick look at items in both lists, select both columns and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every name that appears in both lists gets highlighted. This also flags repeats within one list, so use it for a fast check, not a final answer.

Compare two lists row by row#

If both lists are in the same order and you want to know whether each row matches, compare the cells directly:

=A2=C2

This returns TRUE or FALSE for each row. It ignores capitals, so "ana" equals "Ana". When capitals matter, such as product codes, use =EXACT(A2,C2) instead, which is case-sensitive. Row-by-row checks are common for reconciling two exports of the same report.

Why matching items show as missing#

If an item you can see in both lists shows No or Missing, the two values aren't identical to Excel. The usual causes:

• Extra spaces. "Ben " with a trailing space doesn't match "Ben". Clean both lists with TRIM.
• Numbers stored as text. An ID of 1042 and the text "1042" don't match. Convert them so both are numbers.
• Different spellings. "Dev" and "Devendra" are different values. Agree on one version first.

These are the same issues that break lookups. The lookup errors practice has tasks built around them. For counting the distinct names after you compare, see how to count unique values. If you track sign-ups often, the attendance tracker walkthrough builds this check into a reusable sheet, and COUNTIF vs COUNTIFS explains when to compare on two columns at once. The COUNTIF function guide has a quick syntax reference.

Practise it#

Comparing lists is a very common task in data and operations job tests. Practise COUNTIF in a live spreadsheet: you type the formula, it checks the result instantly, and you learn the criteria rules that make list checks work. Then try the data analyst Excel mock test to compare and clean data under time pressure.