Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Compare Two Columns for Finding Differences in Excel

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

## 7 Ways to Compare Two Columns for Finding Differences in Excel

In this section, you will find 7 ways to compare two columns in Excel for finding differences. I will discuss them one by one here. Stay connected!

So, let’s get started with a simple example, to illustrate how to accomplish this. Here we have two lists where some fruits’ names are placed. We will compare the two lists for finding the differences. The two lists containing the fruits’ names are given below.

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

### 1. Applying Conditional Formatting to Compare Two Columns

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

📌Steps:

• First, select the ranges where you want to apply the conditional formatting. In this example, the range is B5:B11.
• Now, in the Home tab click on Conditional Formatting, and under Highlight Cells Rules click on 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. ### 2. Compare Two Columns Using IF Function

We will use the IF Function to find the differences between two columns. Just follow the steps below for this.

📌Steps:

• First of all, create a new column to show which fruits of List 1 are available in List 2.
• Now, select the first cell (i.e. E5) of the newly created column and apply the following formula.

`=IF(B5=C5,"YES","NO")`

Here,

• B5 = Fruit in List-1
• C5 = Fruit in List-2 • After that, press ENTER, and you will see the statement NO in cell D5.
• Now, use the Fill Handle tool to drag down the formulated and Autofill the formula downwards from cell D5 to D11 • Hence, all the cells will show the result and you can differentiate between the two columns. ### 3. Applying EXACT Function to Compare Columns

The EXACT Function compares two text strings and then returns TRUE or FALSE based on the exact match between the texts. So, you can apply this function for serving the purpose of finding differences between two columns. In order to do so, chase the following steps.

📌Steps:

• First of all, select a cell and type the following formula to the cell.

`=EXACT(B5,C5)`

Here,

• B5 = Fruit in List-1
• C5 = Fruit in List-2 • Then, press ENTER and the cell will return FALSE. • Now, drag the formula down and your cells will show you the result. ### 4. Applying IF with AND Function

A combination of IF and AND functions will serve your purpose. Proceed as below.

📌Steps:

• First of all, apply the formula to a selected cell.

`=IF(AND(B5<>C5),"No Match","Match")`

Here,

• B5 = Fruit in List-1
• C5 = Fruit in List-2 • Then drag the formula down to let the cells show the result. ### 5. Combining IF, ISNA, and VLOOKUP Functions

We can use the IF, ISNA, and VLOOKUP functions to find the differences between two lists or columns in Excel. The procedure is given below.

📌Steps:

• First of all, create a new column> select the first cell (i.e. E5) of the newly created column and apply the following formula.

`=IF(ISNA(VLOOKUP(B5,\$C\$5:\$C\$11,1,0)),"NO","YES")`

Here,

• B5 = Lookup Value
• C5:C11 = Lookup Array 💡 Formula Breakdown

VLOOKUP(B5,\$C\$5:\$C\$11,1,0) looks for the value of B5 (i.e. Apple) in the range \$C\$5:\$C\$11. This value is not available in the lookup array and returns #N/A.

ISNA function checks whether a cell contains the #N/A! error or not. It returns TRUE or FALSE depending on the presence of #N/A!

So, ISNA(VLOOKUP(B5,\$C\$5:\$C\$11,1,0)) =  ISNA(#N/A) returns TRUE.

Finally, IF(ISNA(VLOOKUP(B5,\$C\$5:\$C\$11,1,0)),”NO”,”YES”) IF(ISNA(#N/A),”NO”,”YES”) = IF(TRUE,”NO”,”YES”)  = NO

So, the OUTPUT => NO. That is because the fruit name Apple from List-1 is not available in List-2.

• After that, press ENTER, and you will see the statement NO in cell D5. • Now, use the Fill Handle tool to drag down the formulated and Autofill the formula downwards from cell D5 to D11
• Finally, you will be able to see the differences between List-1 and List-2 ### 6. Use a Combination of IF, ISERROR, and MATCH Functions

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 fruit’s name which is only in List-1. The procedure is given below.

📌Steps:

• First of all, select the first cell D5 of the newly created column and type the following formula to the selected cell.

`=IF((ISERROR(MATCH(B5,\$C\$5:\$C\$11,0))),B5,"")`

Here,

• B5 = Lookup Value
• C5:C11 = Lookup Array 💡 Formula Breakdown

MATCH function looks for the value of B5 (i.e. Apple) in the lookup range \$C\$5:\$C\$11.

So, MATCH(B5,\$C\$5:\$C\$11,0) returns #N/A as it doesn’t find the value in the lookup range.

Now, ISERROR(MATCH(B5,\$C\$5:\$C\$11,0))= ISERROR(#N/A ) returns TRUE.

Finally, IF((ISERROR(MATCH(B5,\$C\$5:\$C\$11,0))),B5,””) = IF(TRUE,B5, “”) returns the value of B5 (i.e. Apple).

So, the OUTPUT =>Apple.

• After pressing ENTER you will see the output in that cell. Now drag the following formula for the next cells. • Hence, the cells in which you have copied the formula will show you the result. • In this same way, you can find the fruit’s name which is only in List-2. In that case, the formula will be,

`=IF((ISERROR(MATCH(C5,\$B\$5:\$B\$11,0))),C5,"")`

Here,

• C5 = Lookup Value
• B5:B17 = Lookup Array ### 7. Combining IF and COUNTIF Functions to Compare Columns

In this procedure, if List-1 contains any fruit 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. We will combine IF and COUNTIF functions for this purpose. Let’s start the comparison.

📌Steps:

• First of all, type the following formula in cell D5.

`=IF(COUNTIF(\$C\$5:\$C\$11, \$B5)=0, "Not Found in List-2", "")` 💡 Formula Breakdown

The COUNTIF function returns the total number of cells in a defined range.

COUNTIF(\$C\$5:\$C\$11, \$B5) looks for the value of cell B5 (i.e. Apple) in the range \$C\$5:\$C\$11 but finds nothing in the range. So, Output=> 0.

• Now, press ENTER to let the cell show the result.
• After that, drag the formula down. • By doing this, you will see the differences between the two columns. ## Conclusion

So, we can see the different processes to compare two columns in Excel for finding differences. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed, using conditional formatting is the best way to compare 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. Keep in touch! #### Siam Hasan Khan

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 an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1. Reply Guillermo J Wainselboim Jul 17, 2017 at 8:12 PM

Good day

My apologies for the inconvenience

Thank you and have a nice day

2. Reply Tanks

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

4. Reply Hallo Kawser,

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

• Reply Hi, PAOLO!
`=IF(ISNA(VLOOKUP(LEFT(A2,4),LEFT(\$B\$2:\$B\$8,4),1,FALSE)),"NO","YES")`
`=IF((ISERROR(MATCH(LEFT(A2,4),LEFT(\$B\$2:\$B\$8,4),0))),A2,"")` 