QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Compare Two Columns to Find Matches & Differences in Excel 2016?

By Neha T Leave a Comment

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.

Compare Two Columns in Excel:

  1. For Exact Row Match
  2. Highlight for Matches or Differences
  3. Using VLOOKUP

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.

compare two columns for exact row match

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.

compare two columns for matches and differences

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.

compare two columns and highlight for exact row match

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.

compare two columns and highlight for exact row match 1

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.

compare two columns and highlight for exact row match 2

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.

compare two columns highlight for matches

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.

compare two columns highlight for matches 1

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.

compare two columns highlight for matches 2

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.

compare two columns highlight the differences

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.

compare two columns highlight the differences 1

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 highlight the differences 2

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.

compare two columns using Vlookup

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 1

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)

compare two columns using Vlookup for exact match

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)

compare two columns using Vlookup for partial match

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.

You Might Also Like:

how-to-use-vlookup-in-ms-excel-2016-featured-imageHow to use VLOOKUP in MS Excel 2016? how-to-split-text-of-cells-in-ms-excel-2016-featured-imageHow to Split Text in a Cell in MS Excel 2016? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? delete blank row feature imageHow to Delete Blank Rows in Excel 2016? how-to-freeze-row-and-column-in-ms-excel-in-2016-featured-imageHow to Freeze Rows and Columns in MS Excel 2016? hide columns feature imageHow to Hide and Unhide Rows & Columns in Excel? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Create a Hotmail Account
  • How to Add Notes in MS PowerPoint 2016?
  • How to Put Signature in Outlook 2007
  • How to create a “Google Apps for Work” account
  • How to Install Google Chrome and Customize it in Ubuntu

Copyright © 2023 · QueHow · Contact Us