How to compare two columns in Excel

In short: if the values sit side by side in the same rows, =A2=B2 is enough. If you need to know whether an entry appears anywhere in the other list, use =COUNTIF($D$2:$D$8,A2)=0, which returns TRUE when it is missing.

Try it now

After a stock count, the system figures and the counted figures are next to each other. Write the comparison in D2. It is filled down to D7 for you.

Task After a stock count you need to check whether the counted stock matches the stock in the system. In D2, write a formula that shows TRUE when B2 and C2 are equal. It is then filled down to D7.

	A	B	C	D
1	Item	Stock in system	Stock counted	Match?
2	Copy paper	120	120
3	Toner	45	44
4	Envelopes	300	300
5	Binders	18	18
6	Ballpoint pens	75	80
7	Sticky notes	60	60

Which formula answers which question

Question	Formula	Returns
Same value in the same row?	=A2=B2	TRUE or FALSE, not case-sensitive
Same value and same case?	=EXACT(A2,B2)	TRUE only for identical text
Flag differences with a word	=IF(A2=B2,"","Mismatch")	blank or "Mismatch"
Is A2 missing from the other list?	=COUNTIF($D$2:$D$8,A2)=0	TRUE if not found
Pull the matching value from the other list	=XLOOKUP(A2,$D$2:$D$8,$E$2:$E$8,"missing")	value from column E or "missing"

A plain = comparison only makes sense when both lists are in the same order. As soon as the order differs, you need a lookup: COUNTIF, XLOOKUP or VLOOKUP.

EXACT for case-sensitive checks

The equals sign ignores case, so ="Miller"="MILLER" is TRUE. For product codes where case carries meaning, or when you audit an import, that is too forgiving. =EXACT(A2,B2) compares character by character and treats upper and lower case as different. It ignores formatting such as bold text or number formats.

Practice: find what is missing

The classic list comparison: which entries from list 1 do not show up in list 2? The lists have different lengths and a different order, so a row-by-row check will not work.

Task Customers were moved from the old system (column A) to the new one (column D). In B2, write a formula that shows TRUE when the customer number in A2 is missing from the list D2:D8. The formula is filled down to B7 and must be right in every row.

	A	B	C	D
1	Customer no. old	Missing in new?		Customer no. new
2	10482			10233
3	10517			10690
4	10233			10482
5	10690			10901
6	10345			10771
7	10771			10555
8				10120

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

To show a word instead of TRUE, wrap it in IF: =IF(COUNTIF($D$2:$D$8,A2)=0,"missing","ok"). The COUNTIF lesson covers how criteria work, and absolute references explains the dollar signs.

VLOOKUP or XLOOKUP to compare two columns

COUNTIF only tells you whether something exists. Often you also want a value from the other list, such as the new price next to the old one. XLOOKUP handles both in one step:

=XLOOKUP(A2,$D$2:$D$8,$E$2:$E$8,"missing")

The fourth argument is what you see when the ID is not found. In versions without XLOOKUP, use =IFERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE),"missing"). Once the looked-up value sits next to your own, compare the two with = again, for example =B2=F2 to ask "has the price stayed the same?".

Highlight differences with conditional formatting

In a long list, color is quicker to scan than a column of TRUE and FALSE. Select the range, say A2:B7, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and enter:

=$A2<>$B2

The dollar sign before the column makes every cell in the row compare the same two values, so the whole row is colored. To flag entries in column A that are missing from column D, the rule is =COUNTIF($D$2:$D$8,$A2)=0. A quicker but rougher option: select both columns and choose Highlight Cells Rules > Duplicate Values. Excel colors everything that appears in both columns, but also repeats within a single column. See conditional formatting for more rules.

Comparing two sheets in Excel

When two versions of the same table live on two sheets, add a third sheet and put =IF(Sheet1!A1<>Sheet2!A1,"Mismatch","") in A1. Drag it across the whole area. Every cell that says "Mismatch" marks a spot where the sheets differ. For lists you reconcile every week, Power Query is more comfortable: a merge with an anti join returns exactly the rows that have no match (see VLOOKUP in Power Query). Microsoft also offers a separate tool called Spreadsheet Compare, but according to its documentation only with Office Professional Plus 2013, 2016 and 2019 and Microsoft 365 Apps for enterprise.

Common mistakes

Numbers stored as text

Data from an export often stores numbers as text. To Excel, 10482 the number and "10482" the text are different, so =A2=B2 returns FALSE, while COUNTIF usually still counts the text version as a match. Two formulas can then give you two different answers. A small green triangle in the cell corner is the usual giveaway. Convert the column first, for example through the warning icon and Convert to Number.

Hidden spaces

"Boston" and "Boston " with a trailing space are not equal. Clean the data with TRIM or compare =TRIM(A2)=TRIM(B2) directly.

The lookup list slides down

Without dollar signs in COUNTIF(D2:D8,A2), the list moves down one row with every row you fill. The top entries drop out of the search, and the formula reports items as missing that are actually there.

These formulas in German Excel

English	German
=EXACT(A2,B2)	=IDENTISCH(A2;B2)
=COUNTIF($D$2:$D$8,A2)=0	=ZÄHLENWENN($D$2:$D$8;A2)=0
XLOOKUP, VLOOKUP, IFERROR	XVERWEIS, SVERWEIS, WENNFEHLER
TRUE, FALSE	WAHR, FALSCH

German Excel separates arguments with semicolons instead of commas.

Which Excel versions support this?

The = comparison, EXACT, COUNTIF, VLOOKUP and conditional formatting work in every current version of Excel. XLOOKUP needs Excel 2021 or later or Microsoft 365. In Excel 2016 and 2019, use VLOOKUP or INDEX with MATCH instead.

FAQ