Excel Compare Two Columns (Matches/Differences)

You might be aware of Excel basics. If not then we recommend you to keep in touch with us as we will teach you everything related to excel. In this post, we are going to perform excel compare two columns for finding out the matches as well as differences available in both the columns. Apart from conditional formatting, you can compare columns using Excel If statement multiple conditions range or vlookup function.

Excel Compare Two Columns for Matches

Excel compare two columns

To find duplicate values by comparing two columns, you need to use conditional formatting excel formula on the columns. There are other ways also like Excel If formula and vlookup function but here we are using conditional formatting.

  • Let take the Employee Id’s who meet their sales target in the year 2017 in Column 1 whereas Employee Id’s of those candidates who meet their targets in the year 2018 are to be taken in Column 2. Now we have to compare column 1 with column 2.
  • Select Column 1 to make it highlight.
  • Go to the conditional formatting option available on the home tab.

Compare two columns in excel

  • By clicking the drop-down list, you will find various conditional formatting options, select New rule.
  • You will find various rule types, in which you need to choose “Use a formula to determine which cells to format” option.
  • In format values where this formula is the true box, you have to enter formula “=countif($B:$B, $A1).
  • Click on the Format button and select the format according to your requirement.

Compare two columns in excel

  • You can fill color in the cell by clicking on the Fill tab and choose the background color.

Compare two columns in excel

  • After making this selection, Press on OK button.
  • After this, you will get the previous sheet with coloring on the duplicate values.

Compare two columns in excel

  • Do the same process for Column 2.

This way you can find out the matches by comparing two columns in excel. Now we will learn to find differences by comparing two columns in excel.

Excel Compare Two Columns for Differences

To find the different values in two columns, we use conditional formatting and using this formatting we highlight all the differing values.

  • We will take the same list as we have taken for the matches. Now we will compare these columns to get the common data and highlighting the different data.
  • Choose the columns that you want to compare.
  • Click on the conditional formatting and you will find the drop-down menu.

Compare two columns in excel

  • In this menu, you need to select the Highlight Cell Rules option.
  • In its submenu, choose More Rules option and click on it.
  • After selecting more rules, you will find another pop up displayed.
  • In this pop-up, choose format only unique or duplicate values.
  • In Format, all drop-down list, choose unique as an option.
  • Then click on format.
  • Go to fill tab, and choose a color.
  • Press Ok.

Compare two columns in excel

  • You will find a coloring sheet that is showing unique items.

How to Compare Two Columns in Excel using VLookup function?

To compare two columns in excel, you can also use the Vlookup function in excel. Here you can check the steps that help you perform the task.

For performing a comparison, we use three functions.

Vlookup: To do research

ISNA: To perform the test

If: To customize the result

Steps to Excel Compare Two Columns

Check the steps to compare two columns using Vlookup function.

  • Let’s take the same data set. Now to get the exact match, you need to apply Vlookup formula

=Vlookup(A2,$B$2:$B$8,1,0)

  • Now customize your result by using ISNA function. Now we will insert ISNA function here

=ISNA(Vlookup(A2,$B$2:$B$8,1,0)

Excel Compare two columns

  • To integrate the test, we use If function:

=If(ISNA(Vlookup(A2,$B$2:$B$8,1,0)),”Missing”,””)

  • Now to find the matches and differences, you can use conditional formatting to color the matched or differed values. For coloring, you can use the same method which is mentioned above.

This is how you can compare two columns with each other using conditional formatting. If you do not want to perform conditional formatting, then you can use the Vlookup function in excel by applying the above formula. Here we have taken the same data set which we have taken for conditional formatting. Now you can apply this step by step process to get the result as Match or Differed value. In the next post, you can learn other excel function. So stay connected with our website.

Related Articles

Leave a Reply

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