Cellwise Try it free

Excel guide · 2 min read

How to compare two columns in Excel

Quick answerTo compare row by row, use =IF(A2=B2,"Match","Different") and fill it down. To check whether a value in column A appears anywhere in column B, use =IF(COUNTIF($B$2:$B$9,A2)>0,"Found","Missing").

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1Last monthThis monthPrice JanPrice Feb
2ApplesApples1.21.2
3BananasCherries0.50.55
4CherriesBananas33
5DatesFigs4.54.25
6GrapesGrapes2.82.8
7KiwisLemons0.90.9
8MangoesMangoes1.751.95
9PearsKiwis1.11.1

First, decide what "compare" means

There are two different questions. Row by row asks "is A2 the same as B2?". List matching asks "does this value appear anywhere in the other column?". Bananas is in both lists here, but on different rows, so the answers differ.

Compare row by row with =

An equals sign between two cells returns TRUE or FALSE. Wrap it in IF for labels people can filter on. This works for text, numbers and dates.

=IF(C5=D5,"Same","Changed")

Result: Changed

Returns "Changed", because the price of dates dropped from 4.5 to 4.25.

Find values from one list that are missing in the other

COUNTIF counts how many times A5 appears anywhere in column B. Zero means it is missing. Lock the range with $ so it does not move as you fill down.

=IF(COUNTIF($B$2:$B$9,A5)>0,"Found","Missing")

Result: Missing

Returns "Missing". Dates is not on this month's list.

The same check with MATCH

MATCH returns a position when it finds the value and an error when it does not. ISNUMBER turns that into TRUE or FALSE. Use whichever reads more naturally to you; the result is the same.

=ISNUMBER(MATCH(A3,$B$2:$B$9,0))

Result: TRUE

Returns TRUE. Bananas appears in column B, just on a different row.

Highlight the differences (no formula column)

Go To Special can select every cell in a row that differs from the first column you selected. Then fill those cells with a colour.

For list matching, use a conditional formatting formula rule instead: select A2:A9, choose Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format", and enter =COUNTIF($B$2:$B$9,A2)=0.

  1. Select both columns, for example C2:D9.
  2. Go to Home > Find & Select > Go To Special.
  3. Choose Row differences and click OK. The shortcut is Ctrl+\.
  4. Pick a fill colour from Home > Fill Color while the cells are still selected.

Common mistakes

Expecting = to be case-sensitive

=A2=B2 treats "apples" and "Apples" as equal. Use =EXACT(A2,B2) when case matters.

Hidden spaces cause false "Different"

A trailing space makes "Kiwis " differ from "Kiwis". Compare cleaned values: =TRIM(A2)=TRIM(B2).

Numbers stored as text never match

A number and the same number as text are not equal with =. Convert the text column with VALUE or Data > Text to Columns > Finish.

The range moves as you fill down

Write $B$2:$B$9 with dollar signs, or press F4 after selecting the range.

FAQ

How do I compare two columns and return a value from a third?

Use a lookup. =XLOOKUP(A2,$B$2:$B$9,$D$2:$D$9,"Not found") returns the matching row's column D value, or a message if A2 is not in column B.

How do I compare two columns for duplicates?

Use =COUNTIF($B$2:$B$9,A2)>0. TRUE means the value is in both lists.

Can I list all values that are missing from the second column?

In Excel 365, =FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)=0) spills every value from column A that is not in column B.

How do I compare two columns in different sheets?

Include the sheet name in the range, like =COUNTIF(Sheet2!$A$2:$A$500,A2)>0. Click across to the other sheet while typing and Excel adds it for you.

Functions used

Related guides

Updated