To compare two columns row by row, type =B2=C2 next to the first row and fill it down: TRUE means the two cells match, FALSE means they differ. To find the values of one column that appear anywhere in another column, in any order, use =COUNTIF($B$2:$B$8,A2)>0 instead.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Old | New | Same? | Status |
| 2 | Apple | $1.20 | $1.20 | TRUE | Same |
| 3 | Pear | $1.50 | $1.60 | FALSE | Changed |
| 4 | Carrot | $0.80 | $0.80 | TRUE | Same |
| 5 | Bread | $2.40 | $2.20 | FALSE | Changed |
| 6 | Milk | $1.10 | $1.10 | TRUE | Same |
| 7 | Cheese | $4.50 | $4.90 | FALSE | Changed |
D3, D5 and D7 are FALSE, and the conditional formatting rule =$B2<>$C2 colours those three rows. Column E shows the same test with words instead of TRUE and FALSE. Change C3 to 1.5 and row 3 turns to Same.
Compare two columns with IF
=B2=C2 returns TRUE or FALSE. Wrap it in IF to choose the words: =IF(B2=C2,"Same","Changed"), as in column E above. To leave matching rows blank and mark only the differences, use =IF(B2<>C2,"Changed",""). To show by how much a number changed, subtract instead of comparing: =C2-B2.
To count the differences without a helper column, compare the two ranges inside SUMPRODUCT: =SUMPRODUCT(--(B2:B7<>C2:C7)) returns 3 for the sheet above.
Without a formula: select B2:C7 with B2 as the active cell, go to Home > Find & Select > Go To Special, choose Row differences and press OK (on Windows, Ctrl+\ does the same). Excel selects C3, C5 and C7, the cells that differ from column B in their row; give them a fill colour to mark them.
Case-sensitive comparison with EXACT
The = comparison ignores upper and lower case: ab12 equals AB12. When case matters (product codes, passwords, IDs), use EXACT(A2,B2), which is TRUE only when the two texts are identical character for character.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Entered | Equal? | EXACT |
| 2 | AB12 | AB12 | TRUE | TRUE |
| 3 | CD34 | cd34 | TRUE | FALSE |
| 4 | EF56 | EF56 | TRUE | TRUE |
| 5 | GH78 | Gh78 | TRUE | FALSE |
Column C, the = comparison, says all four match. EXACT says rows 3 and 5 differ, because cd34 and Gh78 use lower case letters.
Find values in one column that are missing from the other
When the two lists are not in the same order, compare each value with the whole other column. COUNTIF($B$2:$B$8,A2) counts how many times A2 appears in B2:B8, so >0 means "found" and =0 means "missing". The $ signs keep the searched range fixed while the formula is filled down.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | In February? | With MATCH |
| 2 | Ana | Dan | TRUE | TRUE |
| 3 | Ben | Fay | TRUE | TRUE |
| 4 | Cara | Ana | FALSE | FALSE |
| 5 | Dan | Gus | TRUE | TRUE |
| 6 | Eve | Hal | FALSE | FALSE |
| 7 | Fay | Ivy | TRUE | TRUE |
| 8 | Gus | Ben | TRUE | TRUE |
Cara and Eve are FALSE: they bought in January and not in February. MATCH gives the same answer by another route: MATCH(A2,$B$2:$B$8,0) returns the position of A2 in column B, or #N/A when it is not there, and ISNUMBER turns that into TRUE or FALSE. To check the other direction (new customers in February), put the same formula next to column B with the ranges swapped: =COUNTIF($A$2:$A$8,B2)>0.
Compare two lists and return a matching value
Often the question is not only "is it there" but "does the value next to it match". Here, invoices are compared with a list of payments in another order: XLOOKUP finds each invoice in the payments, returns what was paid, and column D compares it with the invoice amount.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Amount | Paid | Match? | Payment for | Paid | |
| 2 | INV-101 | 120 | 120 | TRUE | INV-103 | 240 | |
| 3 | INV-102 | 85 | Not paid | FALSE | INV-101 | 120 | |
| 4 | INV-103 | 240 | 240 | TRUE | INV-105 | 140 | |
| 5 | INV-104 | 60 | 60 | TRUE | INV-106 | 95 | |
| 6 | INV-105 | 150 | 140 | FALSE | INV-104 | 60 | |
| 7 | INV-106 | 95 | 95 | TRUE |
INV-102 has no payment, so C3 says Not paid. INV-105 was paid 140 instead of 150, so D6 is FALSE as well. The last argument of XLOOKUP, "Not paid", replaces the #N/A a missing value would give. XLOOKUP needs Excel 2021 or Microsoft 365; in Excel 2019 use =IFERROR(VLOOKUP(A2,$F$2:$G$6,2,FALSE),"Not paid"). The XLOOKUP page has the other arguments.
List the values missing from the other column
Instead of a TRUE/FALSE column, FILTER can return the missing values as a list. COUNTIF(B2:B8,A2:A8) with a range as its second argument counts every value of A at once, and FILTER keeps the ones where the count is 0.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | Not in February | |
| 2 | Ana | Dan | ||
| 3 | Ben | Fay | ||
| 4 | Cara | Ana | ||
| 5 | Dan | Gus | ||
| 6 | Eve | Hal | ||
| 7 | Fay | Ivy | ||
| 8 | Gus | Ben |
Your turn: In D2, list the January customers who are not in the February list.
The answer spills Cara and Eve. =FILTER(A2:A8,ISNA(MATCH(A2:A8,B2:B8,0))) works too. If every customer came back, FILTER returns #CALC!; add a third argument for that case: =FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0,"None"). FILTER needs Excel 2021 or Microsoft 365. See FILTER for more conditions.
Highlight the differences between two columns
The formulas above work as conditional formatting rules too. Select the first list, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter the formula for its first cell. Here A2:A8 gets =COUNTIF($B$2:$B$8,A2)=0 and B2:B8 gets =COUNTIF($A$2:$A$8,B2)=0: every name that is in only one of the lists is coloured.
| A | B | |
|---|---|---|
| 1 | January | February |
| 2 | Ana | Dan |
| 3 | Ben | Fay |
| 4 | Cara | Ana |
| 5 | Dan | Gus |
| 6 | Eve | Hal |
| 7 | Fay | Ivy |
| 8 | Gus | Ben |
Cara and Eve are coloured in January, Hal and Ivy in February. For two columns that should match row by row, the rule is =$A2<>$B2 on both columns, as in the first sheet on this page. To colour the names that are in both lists instead, use >0, as on the [highlight duplicates](/docs/excel/highlight-duplicates) page.
Why identical values show as different
The most common reason is a space you cannot see: Ana with a trailing space is not equal to Ana. Data pasted from another system or a web page often carries them. Compare the trimmed values instead.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Other list | Equal? | Trimmed |
| 2 | Ana | Ana | FALSE | TRUE |
| 3 | Ben | Ben | TRUE | TRUE |
| 4 | Cara | Cara | FALSE | TRUE |
| 5 | Dan | Dan | TRUE | TRUE |
Column C says rows 2 and 4 differ; column D, after TRIM removes the spaces at both ends, says all four match. The other usual reason is a number stored as text in one column and a real number in the other: 101 and '101 look the same, but Excel's = comparison returns FALSE, and MATCH, VLOOKUP and XLOOKUP do not find one in the other. COUNTIF is the exception: it reads text that looks like a number as that number, so it counts them as equal. A green triangle in the cell's corner marks the text version; convert it with =VALUE(A2) or =A2*1, or select the cells and choose Convert to Number from the warning icon.
Frequently Asked Questions
How do I compare two columns in Excel for matches?
Row by row: type =A2=B2 in C2 and fill it down; TRUE means the two cells match. To check whether each value of A appears anywhere in B, use =COUNTIF($B$2:$B$8,A2)>0.
How do I compare two columns and return a value from the second?
Look the value up: =XLOOKUP(A2,$F$2:$F$7,$G$2:$G$7,"Not found") returns the matching value from G, or Not found. In Excel 2019 and older use =IFERROR(VLOOKUP(A2,$F$2:$G$7,2,FALSE),"Not found").
Is comparing two cells in Excel case-sensitive?
No. =A2=B2 treats abc and ABC as equal. For a case-sensitive comparison use =EXACT(A2,B2), which is TRUE only when every character matches, case included.
How do I list the values that are in one column but not the other?
In Excel 365 and 2021, =FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0) spills every value of A2:A8 that does not appear in B2:B8.
Why does Excel say two identical values are different?
One of them usually has an extra space or is a number stored as text. Compare =TRIM(A2)=TRIM(B2) to rule out spaces, and convert text numbers with =VALUE(A2) or =A2*1.
