How to Compare Two Columns in Excel (for matches & differences)

To compare two columns in Excel for matches and differences, you can use various methods, including:

Method 1: Using Conditional Formatting

1. Select the range of cells that you want to compare.
2. Click on the “Home” tab in the ribbon, and select “Conditional Formatting” from the “Styles” group.
3. Choose “Highlight Cells Rules,” and then select “Duplicate Values.”
4. In the “Duplicate Values” dialog box, make sure the “Duplicate” option is selected, and choose the formatting you want to use to highlight the matches.
5. Click OK to apply the formatting.

This will highlight the matching values in the selected range.

Method 2: Using the COUNTIF Function

1. Enter the formula =COUNTIF(Range1,Range2) in a cell outside the two ranges you want to compare, where “Range1” is the first range of cells you want to compare, and “Range2” is the second range of cells you want to compare.
2. Press Enter to apply the formula.
3. The result will be the number of times the values in Range2 appear in Range1.

You can also use the formula =COUNTIF(Range2,Range1) to count the number of times the values in Range1 appear in Range2.

Method 3: Using the VLOOKUP Function

1. Enter the formula =IF(ISNA(VLOOKUP(Range2,Range1,1,FALSE)),"Not Found","Match") in a cell outside the two ranges you want to compare, where “Range1” is the first range of cells you want to compare, and “Range2” is the second range of cells you want to compare.
2. Press Enter to apply the formula.
3. The result will be “Match” if the value in Range2 is found in Range1, or “Not Found” if the value is not found.

You can also use the formula =IF(ISNA(VLOOKUP(Range1,Range2,1,FALSE)),"Not Found","Match") to compare Range1 with Range2.

These methods can help you quickly and easily compare two columns in Excel for matches and differences.