Compare two columns in Excel: find matches and differences

Check two lists row by row, or find which names in one list are missing from the other. Formulas with =, EXACT, COUNTIF and XLOOKUP, with a worked example.

Published 19 October 2026. 7 min read.

By Ali, Founder, Excel Lessons

To compare two columns in Excel row by row, type =B2=C2 and fill it down. TRUE means the two cells match. To find items in one list that are missing from the other, use =COUNTIF(list,A2). A 0 means it is missing.

Those are two different jobs, and you need to know which one you have. This article follows a stock count in a small warehouse. The system says how many of each item there should be. Someone counts the shelves. You want the rows that differ, and the items nobody counted.

Same rows: one equals sign

Here the count was typed next to each row, so both columns are in the same order. That is the easy case.

Row	A	B	C	D
1	Code	System	Counted	Match
2	AB-101	40	40	TRUE
3	AB-102	25	23	FALSE
4	AB-103	12	12	TRUE
5	AB-104	8	10	FALSE
6	AB-105	30	30	TRUE
7	AB-106	15	15	TRUE

In D2, type =B2=C2 and fill it down to D7. The first equals sign starts the formula. The second one asks a question.

D3 and D5 show FALSE. Those are the 2 rows to check on the shelf.

A column of TRUE and FALSE is fine for you. For a report, three more formulas help.

Count the differences: =COUNTIF(D2:D7,FALSE) gives 2.
A word, not FALSE: =IF(B2=C2,"","Check") shows Check on rows 3 and 5. The other rows stay empty, so your eye goes to the problems.
The size of the gap: =C3-B3 gives -2. The shelf has 2 fewer than the system expects. In row 5, =C5-B5 gives 2.

What goes wrong: this only works when both columns are in the same order. Sort one column and not the other, and nearly every row shows FALSE. Read 2 or 3 rows by eye before you trust the column.

When capitals matter: EXACT

The equals sign ignores capitals. ="AB-101"="ab-101" returns TRUE. For stock codes and names, that is what you want.

When capitals carry meaning, use EXACT. =EXACT("AB-101","ab-101") returns FALSE. A list of case-sensitive voucher codes is one example. Most people never need it.

Different order: is it on the other list?

Next month the count comes from a scanner. The scanner file lists codes in the order they were scanned. Here the system list is in A and B, and the scanner list is in E and F.

Row	A	B	E	F
1	Code	System	Scanned	Counted
2	AB-101	40	AB-104	10
3	AB-102	25	AB-101	40
4	AB-103	12	AB-107	6
5	AB-104	8	AB-103	12
6	AB-105	30	AB-105	30
7	AB-106	15	AB-102	23

A row-by-row check is useless here. Row 2 holds AB-101 on the left and AB-104 on the right. So you ask a different question: does this code appear anywhere in the other list?

In C2, type =COUNTIF($E$2:$E$7,A2) and fill it down. It counts how many times the code appears in the scanner list.

C2 to C6 give 1. C7 gives 0, so AB-106 was never scanned. The dollar signs keep the list in place while A2 moves down.

For a word, use =IF(COUNTIF($E$2:$E$7,A2)=0,"Not scanned",""). C7 shows Not scanned.

Always check the other direction too

This is the mistake people make most. The check above finds codes in the system that were not scanned. It says nothing about codes that were scanned but are not in the system.

In G2, type =IF(COUNTIF($A$2:$A$7,E2)=0,"Not in system","") and fill it down to G7.

G4 shows Not in system. AB-107 is on a shelf, but the system does not know about it. The first check would never find that.

Bring the number across with XLOOKUP

COUNTIF says whether a code is there. For a stock count you also want the counted number next to the system number. XLOOKUP finds the code and brings back the value from the same row.

In C2, type =XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Not scanned") and fill it down. Then in D2, type =B2=C2 and fill that down too.

Row	A	B	C	D
1	Code	System	Counted	Match
2	AB-101	40	40	TRUE
3	AB-102	25	23	FALSE
4	AB-103	12	12	TRUE
5	AB-104	8	10	FALSE
6	AB-105	30	30	TRUE
7	AB-106	15	Not scanned	FALSE

Now one column answers both questions. D3 and D5 are wrong counts. D7 is an item nobody counted. The fourth part of XLOOKUP is the text to show when the code is missing. Without it, C7 shows #N/A.

XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. It also works in Excel for the web. In Excel 2019 and older, use INDEX and MATCH to bring the value across. The INDEX and MATCH article shows how.

For a plain TRUE or FALSE in any version, =ISNUMBER(MATCH(A2,$E$2:$E$7,0)) works too. It gives TRUE when MATCH finds a position. C7 gives FALSE. COUNTIF is easier to read, so I use that.

A quick look with colour

Sometimes you only want to see the odd ones, not build a report. Select A2:A7, hold Ctrl, and select E2:E7. Then click Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.

In the box, change Duplicate to Unique. The codes that appear in only one list change colour. Here that is AB-106 and AB-107.

The catch: a code typed twice in the same list also counts as a duplicate. And colour cannot be counted or sent as a list. Use it for a look, and use COUNTIF for the answer.

Two cells that look the same but do not match

Say the scanner wrote AB-101 in E3 with a space after it. You cannot see the space, but =LEN(E3) gives 7, not 6. Now both checks go wrong. C2 says Not scanned, and G3 says Not in system.

Wrap the messy value in TRIM. =IF(COUNTIF($A$2:$A$7,TRIM(E3))=0,"Not in system","") shows nothing in G3, because the code is found. TRIM removes spaces at the start and the end.

C2 still says Not scanned, because the space is inside the list it searches. Clean column E once, and both checks are right again.

Another cause is a number in one list and the same number as text in the other. They look the same and never match. The #N/A article covers both causes. The clean-up text article shows how to fix a whole column.

Which one to use

Same order, same rows: =B2=C2. Nothing else is needed.
Different order, yes or no: COUNTIF. It works in every version of Excel.
Different order, and you need the value: XLOOKUP, or INDEX and MATCH in older Excel.

Use COUNTIF unless you need a number from the other list. And run it both ways, every time.

The lessons below practise comparisons, COUNTIF and XLOOKUP. Each lesson is a real sheet you type into, and each answer is checked when you press Enter. More in Excel practice.