How to Compare Two Columns in Excel and finding the even and odds

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.

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.

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.

• 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.

• Press OK to confirm the conditional formatting.

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 

  Source code

  =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 & 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 

  Source code

  =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,

  Source code

  =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.

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

  Source code

  =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.

Courtesy By: www.exceldemy.com