How to compare two columns in Excel and find matches and differences

You have two lists in Excel, for example the product codes in your inventory and the ones listed in your online catalog, and you need to know which are in both, which are missing from one and which are extra. Checking by eye works with ten rows; with five hundred, something will slip through.

Here are the most useful methods for comparing two columns, from row-by-row comparison to extracting the differences automatically, with formulas you can copy as they are.

The sample data

We’ll use this sheet: column A holds the inventory codes and column B the online catalog codes. Headers are in row 1 and the data runs from row 2 to row 7.

Before choosing a method, ask yourself: does position matter? If each row represents the same thing in both columns (for example, the old and new price of a product), compare row by row. If they’re two independent lists in a different order, you need to look up each value in the other column.

Compare row by row

The simplest way is to type an equality in C2 and fill it down:

=A2=B2

It returns TRUE or FALSE. If you’d rather see clearer text:

=IF(A2=B2,"Same","Different")

With the sample data, only rows 2 and 4 come out as “Same”. Notice that P-102 and P-104 appear in both columns, but on different rows, so this method marks them as different. That’s why it only works when the rows are aligned.

Telling upper and lower case apart

The = operator doesn’t distinguish “abc” from “ABC”. If they need to count as different, use EXACT:

=IF(EXACT(A2,B2),"Same","Different")

Check whether a value is in the other column

When order doesn’t matter, the key tool is COUNTIF. Type in C2:

=IF(COUNTIF($B$2:$B$7,A2)>0,"In catalog","Missing from catalog")

The formula counts how many times A2 appears in B2:B7. If it’s more than zero, the code is there. The $ signs lock the range so it doesn’t shift when you copy the formula. Result: P-101, P-102, P-103 and P-104 are in the catalog; P-105 and P-106 are missing.

For the opposite direction (what’s in the catalog but not in the inventory), type in D2 and fill down:

=IF(COUNTIF($A$2:$A$7,B2)=0,"Not in inventory","")

That’s how you find P-107 and P-108.

An alternative with MATCH

If you prefer lookup functions, this formula gives the same result:

=IF(ISNUMBER(MATCH(A2,$B$2:$B$7,0)),"Yes","No")

MATCH returns the position of the value or #N/A if it isn’t found, and ISNUMBER turns that into TRUE or FALSE. If you also want to bring back data from the other list, such as the published price in a column C of the catalog, use VLOOKUP or, in Excel 365 and 2021, XLOOKUP.

Highlight matches and differences with conditional formatting

If you’d rather see colours than a helper column, conditional formatting is ideal.

Mark values that are in both columns

1. Select A2:B7.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
3. Choose a colour and click OK.

Excel colours every value that appears more than once in the selection. Careful: if one column has repeats, they’ll be marked too, even if they aren’t in the other column.

Mark only what’s missing from the other column

1. Select A2:A7, starting from A2.
2. Go to Home › Conditional Formatting › New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type the formula below, click Format, choose a fill and confirm.

=COUNTIF($B$2:$B$7,A2)=0

Only P-105 and P-106 will be coloured. Repeat the process on B2:B7 with =COUNTIF($A$2:$A$7,B2)=0 to mark P-107 and P-108.

Extract the differences into a separate list

In Excel 365 and Excel 2021, the FILTER function directly returns the list of values that meet a condition. To get the inventory codes that aren’t in the catalog, type in an empty cell:

=FILTER(A2:A7,COUNTIF(B2:B7,A2:A7)=0,"None")

The result spills down with P-105 and P-106. For the matches, change the condition:

=FILTER(A2:A7,COUNTIF(B2:B7,A2:A7)>0,"None")

If your data grows, turn the range into a table with Ctrl+T or extend the ranges. Avoid including empty rows: COUNTIF also compares blank cells and could add empty results. In older versions, use the COUNTIF helper column and apply a filter to it from Data › Filter.

Common mistakes when comparing columns

• Invisible spaces. “P-101 ” with a trailing space isn’t equal to “P-101”. Clean them first with =TRIM(A2) in a helper column.
• Numbers stored as text. 101 as a number and “101” as text don’t match. If you see a green triangle in the corner of the cell, convert them with the Convert to Number option.
• Wildcards in the data. COUNTIF treats * and ? as wildcards. If your codes contain them, use MATCH with an equality test or EXACT.
• Forgetting the $ signs. Without absolute references, the range shifts as you copy the formula and the results are no longer reliable.
• Duplicates within the same list. They distort counts and the duplicate values format. If a column has repeats, remove them first with our duplicate remover.

When you need to compare whole files

Formulas work very well with two columns on the same sheet. But if what you have are two versions of a file, for example last month’s price list and the current one, with many columns and rows, building formulas for every field becomes tedious. In that case you can use our free tool to compare Excel files: upload the original file and the modified one and get a new file with the changes between them, without writing a single formula.

Conclusion

To compare two columns, first decide whether position matters. If the rows are aligned, =A2=B2 or IF is enough. If they’re independent lists, COUNTIF tells you what’s in both and what’s missing, conditional formatting makes it visible, and FILTER gives you the differences in a clean list. And before anything else, check for spaces, data types and duplicates: they cause most of the odd results.

Frequently asked questions