How to Compare Two Columns to Find Matches & Differences in Excel 2016? (with Pictures )

There can be two reasons to compare two columns in excel either we have to find the matches or to find the differences. Matches are the data points that are present in both the compared columns. Differences are the data points that are in one column but not present in other column being compared.

There are several ways to compare two columns in an excel sheet. In this section, we will discuss some inbuilt functions, and formulas to compare two columns to discover the matches or the differences. The events that we will discuss here are given below.

Steps to Compare Two Columns in Excel 2016:

For Exact Row Match:

Look at the picture below, we have column 1 and column 2 to be compared. We will compare these columns by typing a simple formula “=A2=B2” in a cell just beside the first row, of both the compared columns and drag it down till the entries in columns doesn’t end up.

This formula returns TRUE if a row has exact same data in both the columns and FALSE if a row has different data in both the columns.

Compare Two Columns Using IF()

We have one more variation of this, we can find the exact match using IF() function this allows us to get an organized result. The formula to compare columns for an exact row match using IF():

=IF(A2=B2, “Match”, “Different”) // press enter and drag it down till the entries of compared columns end up.

This formula returns “Match” if an exact match is found and “Different” if the row has different data in both the column.

Highlight the Rows with Exact Match

Now, we will compare two columns for an exact row match using an inbuilt function.

Step 1: First select the columns to be compared and at Home tab under Styles group, we have Conditional Formatting drop-down menu. Here, select New Rule.

Step 2: A New Formatting Rule dialog box pop out. In the “Select a Rule Type:” box click on “Use a formula to determine which cells to format”.

In “Edit the Rule Description” box, type the formula to identify the cells that have to be formatted.

So, we will type the formula to find the exact match. Also, decide the Format of the cells.

If the formula results True, the cells will be formatted as you describe. Observe the picture below, where the rows which have an exact match for both the columns have been formatted as described in the New Formatting Rule box.

Highlight for Matches or Differences

Highlight the Matches

Till now we have compared the columns row by row. Now, we compare columns to find if a data point in column 1 is present anywhere in column 2. This will be a column-wise comparison.

Step 1: Select the columns that you want to compare and at the Home tab, in the Styles group, we have Conditional Formatting drop-down menu. Here, click on Highlight cells Rules>Duplicate Values.

Step 2: Duplicate Values dialog box will pop out. Here, in the first drop-down menu make sure you have selected Duplicate and in the second drop-down menu select the way you want to format the cells containing the duplicates.

Click OK.

Step 3: See in the picture below, the data points in Column 1 that are also present in Column 2 get formatted in the red background as we have described in the Duplicate Values dialog box.

Highlight the Differences

For highlighting the differences i.e. highlighting the data points that are present in Column 1 but not in column 2 and the data points that are in column 2 and not in column 1 follow the steps below:

Step 1: Select the columns to be compared and at the Home tab in the Styles group click the Conditional Formatting dropdown menu. Further, click on Highlight Cells Rules > Duplicate Values.

Step 2: Duplicate Values dialog box will pop out. Here, make sure in the first drop-down menu Unique is selected and select the format for the cells having different data points.

Click OK.

Step 3: See in the picture below, the data points in column 1 that are not present in column 2 are highlighted with the selected format. Similarly, the data points in column 2 that are not present in column 1 are also highlighted with the selected format.

Compare Two Columns Using VLOOKUP

Step 1: Besides the first row, of the columns to be compared type the VLOOKUP formula.

For example:

=VLOOKUP(A2, $B$2:$B$12, 1,0) // Drag the cell containing the formula till the entries of the compare columns end up.

This formula would compare lookup value with each data point from the range B2:B12 and return the data points of column 1 from the range if its exact match has been found.

Observe the picture above, VLOOKUP return #N/A for the lookup values which are not present in the range.

Step 2: To organize the result in a better way we will modify the formula:

=IFERROR(VLOOKUP(A2, $B$2:$B$12, 1,0), “–” )

This modified formula would display “—” in place of “#N/A”.

Compare Two Columns Using VLOOKUP (Pull Up the Related Data if Match Found)

Now, we know that the VLOOKUP formula returns the data points of the column mentioned in the formula. So, the formula to pull up the related data for the lookup value in VLOOKUP is:

=VLOOKUP(C2, $B$2:$B$12, 2, 0)

This formula looks for a data point in cell C2 in the range A2:B12 and return the data point from the second column in the range if the exact match is found.

But, this formula returns the data point form the second column in the range only if the exact match is found. For example, when the formula checks for a data point in C3 i.e. Davis, we do have Davis in lookup range but along with the last name of Davis i.e. Clawson. But, the formula returns #N/A for it. Similar is the case with Annie, we do have Annie in the lookup range but, along with its last name i.e. Rotus. But, the formula returns #N/A for Annie also.

We can also get a result for it by modifying the formula above:

=VLOOKUP(C2&“*”,$A$2:$B$12,2,0)

As we have appended “*” along with lookup value, it will consider the text after the lookup value and will return the result as shown in the picture above.

So, this is all about how you can compare the two columns in the excel sheet to find matches or differences. But, you can explore more and find out the other methods for the same.