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.

**Table of Contents**hide

**Download Practice Workbook**

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

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

### 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**

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

### 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**.

Finally, **IF(COUNTIF($C$5:$C$11, $B5)=0, “Not Found in List-2”, “”) **= **IF(0, “Not Found in List-2”, “”) **will return “**Not Found in List-2**” when the condition is **0**, otherwise keep the cell blank (**“”**).

So, final Output=> “**Not Found in List-2**“.

- 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!

Good day

My apologies for the inconvenience

The files are currently not available for review/download

Thank you and have a nice day

Working Files are not found.

Could you resend alive links?

Tanks

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

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

Hi, PAOLO!

Thank you for your query.

You have asked about a fantastic thing. If we want to compare the first 6 numbers/match, rather than the full cell value, we have to use the

LEFTfunction in addition to the formulas.So, I would suggest you use the second and third methods shown in this article and use the nested

LEFTfunction inside the VLOOKUP function for the 2nd method and inside the MATCH function for the 3rd method.So, for the second method, the formula would be:

`=IF(ISNA(VLOOKUP(LEFT(A2,4),LEFT($B$2:$B$8,4),1,FALSE)),"NO","YES")`

And, for the third method, the formula would be:

`=IF((ISERROR(MATCH(LEFT(A2,4),LEFT($B$2:$B$8,4),0))),A2,"")`

Have a nice day!