How to Compare Two Columns in Excel For Finding Differences

Last updated on May 13th, 2018

This is an interesting situation that often comes up. Namely, sometimes one needs to differentiate data in two different columns. There are so many processes in which Excel compare two lists and return differences. In this article, we will see the ways on how to compare two columns in Excel for differences.

So, let’s get started with a simple example, to illustrate how to accomplish this.

Introduction

Here we have two lists where some fruits name is placed. We will compare the two lists for finding the differences. The two lists containing the fruits name is given below.

We will see 4 different processes of finding the differences between two columns. In every process, we will use the same table.

Read More: How to Multiply Columns, Cells, Rows, & Numbers in Excel

Compare Two Columns in Excel For Differences using Conditional Formatting

We can use the conditional formatting to highlight the unique values of two columns. The procedure is simple and given below.

Learn Excel Online: Top Excel Courses Online
  • First, select the ranges where you want to apply the conditional formatting. In this example, the range is A2:B8

  • Now, in the Home Tab click on to the Conditional Formatting and Under Highlight Cells Rules click on to Duplicate Values.

  • In the Duplicate values Dialogue Box if you select Duplicate you will see the duplicate values of the two cells.

  • If you select Unique in the Duplicate values Dialogue Box you will see the unique values of the two cells.

Compare Two Columns in Excel For Differences

  • Press OK to confirm the conditional formatting.

Read More: How to Compare Two Columns or Lists in Excel

Compare Two Columns in Excel Using VLOOKUP

We can use the VLOOKUP function in Excel to find the differences between two lists or columns. The procedure is given below.

  • In Cell C2 use the formula 
    =IF(ISNA(VLOOKUP(A2,$B$2:$B$8,1,0)),"NO","YES")
    and press Enter.

  • After pressing Enter, you will see the statement NO in cell C2. That is because the fruit name Apple from List-1 is not available in List-2.

  • Now drag down the formulated cell downwards from C2 to C8 to see the differences between List-1 and List-2.

Compare Two Columns in Excel Using VLOOKUP

Read More: How to Compare Two Columns in Excel Using VLOOKUP

Compare Two Columns in Excel & Returns the Difference

Here we will be using IF, ISERROR, and MATCH functions to compare two columns. We will compare List-1 with List-2. The formula will calculate the two lists and will return the fruits name which is only in List-1. The procedure is given below.

  • In Cell C2, write the formula 
    =IF((ISERROR(MATCH(A2,$B$2:$B$14,0))),A2,"")
    and press enter.

  • After pressing Enter you will see the name of the fruit Apple which is only placed in List-1.

  • Now drag down this formulated cell to see the fruits name which is only in List-1.

  • In this same way, you can find the fruits name which is only in List-2. In that case, the formula will be,
    =IF((ISERROR(MATCH(B2,$A$2:$A$15,0))),B2,"")
     which will be written in cell D2 and should be dragged down to find the comparison between two lists.

compare two columns in excel for differences

Read More: How to Highlight Every Other Row in Excel

How to Compare Two Columns in Excel For Differences Using Formula

In this procedure, if List-1 contains any fruits name which is not placed in List-2, the formula that we will be using will say that the fruit name from List-1 is not found in List-2. Let`s start the comparison.

  • In cell C2 write the formula
    =IF(COUNTIF($B$2:$B$8, $A2)=0, "Not Found in List-2", "")
     and press enter.

  • After pressing Enter you will see the statement, Not Found in List-2 has appeared. It is because the fruit name Apple from List-1 is not in List-2.

  • Now, drag down the formulated cell C2 downwards to copy this formula for the rest of the cells of column C. By doing this, you will see the differences between the two columns.

compare two columns in excel for differences

Download Working Files

Conclusion

So, we can see the different processes of finding the differences between two columns. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed using the conditional formatting is the best way to compare between two columns. Because in conditional formatting you can compare between multiple columns, the procedure is simple and fast and you can find both matches and differences.

Hope you find this article helpful. Browse our website for finding more useful articles.

Read More…

Calculate The Number of Days/Weeks/Months/Years between Two Dates in Excel

How to Lock and Unlock Certain/Specific Cells in Excel

How to Insert or Delete Rows and Columns from Excel Table


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

4 Comments
  1. Reply Guillermo.J.Wainselboim@IRS.Gov'
    Guillermo J Wainselboim July 17, 2017 at 8:12 PM

    Good day

    My apologies for the inconvenience

    The files are currently not available for review/download

    Thank you and have a nice day

  2. Reply haivh.neu@gmail.com'
    Hai July 17, 2017 at 8:53 PM

    Working Files are not found.
    Could you resend alive links?
    Tanks

  3. Reply
    Kawser July 18, 2017 at 2:40 PM

    Thanks for notifying me. It was a technical mistake. I’ve uploaded them and you can download now. Thanks.

  4. Reply p.distefano@gmail.com'
    Paolo January 21, 2018 at 4:41 AM

    Hallo Kawser,

    This seems the path for an exact match, in case I would be ok with a first 6 numbers/characters match?

    Thank you in advance

    Leave a reply