To compare two lists and return differences in Excel means the comparison of two lists or columns and showing the mismatched values in each of the lists.

Let’s say, you are organizing an official event and inviting your colleagues through emails. You have 2 lists: A list of all employees and a list of invited guests. You doubt that you have missed some of the employees and they haven’t received any invitation email yet. So, you want to find them out. In the image below, you see the missed employees who are not invited in column E.

Here, we will show how to compare 2 such lists and return the differences in Excel.

After reading this post, you will learn how to compare 2 lists and return differences using:

– **IF** and **COUNTIF** functions in all Excel versions.

– An **INDEX**–**MATCH** formula

– **IF**, **ISNA** and **MATCH** functions

– **TEXTJOIN** and other functions to get the mismatched values in one cell

– **FILTER** and **COUNTIF** functions in Excel 2019 or later versions

– A VBA Macro

We have also shown how to:

– Make your lists dynamic, so your formulas keep returning different values from the lists when you add new items to these lists.

– Compare two Excel datasets with any number of columns and return whole rows for differences.

– Highlight the differences in 2 lists using Excel conditional formatting.

**Note:** We have used using Microsoft 365 while preparing this tutorial.

## 1. Use Formula with IF & COUNTIF to Compare 2 Lists and Return Differences in All Excel Versions

In the first example, we will use the **IF **and **COUNTIF **functions to compare two lists and return differences in separate cells.

Let’s consider the following dataset. It contains two simple lists of some names.

Not all names of List-1 exist in List-2 and vice versa. For example, George and Bill are not in List-2.

Excel **IF **and **COUNTIF **functions are available in all Excel versions, even if you are using older versions such as Excel 1997, 2003, 2007, etc.

__Now Follow These Steps:__

- Insert the following formula in the
**E6**cell and press**Enter**.

`=IF(COUNTIF($C$6:$C$15,B6)=0,B6,"")`

- Then copy this formula for the rest of the cells below. You will get the names that are in List-1 but not in List-2.

- Similarly, by using the following formula in the
**F6**cell, you will get the names that are in List-2 but not in List-1.

`=IF(COUNTIF($B$6:$B$15,C6)=0,C6,"")`

## 2. Use INDEX-MATCH Formula to Get Differences from 2 Columns (Non-Array Formula)

When you enter this formula in a cell (here, in **cell E6**), it will return the first difference from List-1.

`=INDEX($B$6:$B$15, SMALL(IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), ROW($B$6:$B$15)-MIN(ROW($B$6:$B$15))+1, ""), ROW(1:1)))`

To get all the next differences, you have to copy the formula down until it shows **#NUM! **Error.

If you don’t want to see those **#NUM!** error, use the following formula instead. Here we have used the **IFERROR **function to get an empty string when the** INDEX-MATCH **formula starts to return **#NUM!** Errors.

`=IFERROR(INDEX($B$6:$B$15, SMALL(IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), ROW($B$6:$B$15)-MIN(ROW($B$6:$B$15))+1, ""), ROW(1:1))), "")`

So, this way you can find the names that are in List-1 but not in List-2.

If you want to know which names are in List 2 but not in List 1, you can use the same formula with a few changes.

Replace **$C$6:$C$15** with** $B$6:$B$15** and **$B$6:$B$15** with **$C$6:$C$15** in your formula.

Then the formula becomes:

`=IFERROR(INDEX($B$6:$B$15, SMALL(IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), ROW($B$6:$B$15)-MIN(ROW($B$6:$B$15))+1, ""), ROW(1:1))), "")`

Insert the formula above in **cell F6** and drag the **Fill handle** tool to get all names in List 2, that do not exist in List 1.

Here we have summarised the final outputs in a single image.

__Get Your Differences in A to Z Order__

You can use the following formula to get these differences in A to Z order.

Insert the formula in the **E6 **cell and drag the **Fill Handle** down.

`=IFERROR(INDEX($B$6:$B$15,MATCH(SMALL(IF((COUNTIF($E$5:E5,$B$6:$B$15)+COUNTIF($C$6:$C$15,$B$6:$B$15))=0,COUNTIF($B$6:$B$15,"<"&$B$6:$B$15)),1),COUNTIF($B$6:$B$15,"<"&$B$6:$B$15),0)),"")`

Now the different names are in alphabetical order (Bill ⇒ Chris ⇒ Dan ⇒ George).

**Read More:** Excel formula to compare two columns and return a value

## 3. Use IF, ISNA & MATCH Functions to Compare and Get Differences from 2 Columns with One Click (Array Formula)

You can create an array formula with **IF**, **ISNA, **and **MATCH **functions. If you put this formula in a cell (**cell E6**), then it will return all differences instantly.

`=IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), $B$6:$B$15, "")`

**Note**

- This is an array formula and hence it will display
**#SPILL!**error when merged or non-blank cells exist in**E6:E15**or**F6:F15**. - This formula cannot return all the differences together in adjacent cells. To solve this, you can add
**the SORT function**with this formula if you have Excel 2021 or 365. Then the formula will take the following look.

`=SORT(IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), $B$6:$B$15, ""),1,-1)`

## 4. Combine TEXTJOIN with IF-ISNA-MATCH Formula to Get All Unmatched Values in a Single Cell

You can get all the differences of 2 columns in a single cell by combining **the TEXTJOIN function** with the** IF-MATCH-ISNA** formula. The **TEXTJOIN **function concatenates values with specified delimiters.

*Note:**The*

**TEXTJOIN**Function is available from Excel 2019 version.Write the formula in the **E6 **cell and press **Enter**.

`=TEXTJOIN(", ", TRUE, IF(ISNA(MATCH($B$6:$B$15, $C$6:$C$15, 0)), $B$6:$B$15, ""))`

## 5. Use FILTER & COUNTIF Functions in Excel 2019 or Later Versions

The **FILTER-COUNTIF** formula can be an excellent alternative to previous methods to compare two lists and return differences in Excel. Besides, it is the simplest formula we have so far.

Simply apply the formula as follows in the **E6 **cell and hit **Enter**.

`=FILTER(B6:B15,COUNTIF(C6:C15,B6:B15)=0)`

## 6. Use a VBA Code to Compare Two Lists and Return Differences

In this section, we will discuss how to compare any two selected lists and return differences by applying **VBA Macro**.

The previous examples were for a dataset where we needed to develop a formula considering the position of values. However, the **VBA **code in this section is applicable to any two lists.

- Write the VBA code in the module and
**save**the Macro.

```
Sub Compare_Two_Lists_and_Return_Differences()
‘Developed by MD_Tanvir_Rahman, ExcelDemy
Dim rng As Range
Dim Output_rng As Range
Dim i As Integer
Dim j As Integer
Dim outputRow1 As Integer
Dim outputRow2 As Integer
Set rng = Application.InputBox("Select Input Range:", Type:=8)
Set Output_rng = Application.InputBox("Select Output Range:", Type:=8)
outputRow1 = 1
outputRow2 = 1
For i = 1 To rng.Rows.Count
Dim foundMatch1 As Boolean
foundMatch = False
For j = 1 To rng.Rows.Count
If rng.Cells(i, 1).Value = rng.Cells(j, 2).Value Then
foundMatch = True
Exit For
End If
Next j
If Not foundMatch Then
Output_rng.Cells(outputRow1, 1).Value = rng.Cells(i, 1).Value
outputRow1 = outputRow1 + 1
End If
Next i
For i = 1 To rng.Rows.Count
Dim foundMatch2 As Boolean
foundMatch = False
For j = 1 To rng.Rows.Count
If rng.Cells(i, 2).Value = rng.Cells(j, 1).Value Then
foundMatch = True
Exit For
End If
Next j
If Not foundMatch Then
Output_rng.Cells(outputRow2, 2).Value = rng.Cells(i, 2).Value
outputRow2 = outputRow2 + 1
End If
Next i
End Sub
```

- Now, click as follows:
**Developer**=>**Macros**=>**Compare_Two_Lists_and_Return_Differences**=>**Run**.

Thus an **Input **dialog box shows up. Select the **$B$6:$C$15** range => **OK**.

- Again another
**Input**dialog box shows up. Select the**$E$6**range =>**OK**.

- Finally, we obtain all the differences of both lists in the
**E6:F9**range.

**Note**

*The***VBA Macro**doesn’t update data automatically. You must**Run**the macro every time the data changes.*We used***Conditional Formatting**to highlight the differences, not the statements inside VBA Macro.

## How to Create a Dynamic Formula That Compares 2 Columns and Return Differences When You Add New Items to Your Lists

Say, you want a formula to compare your columns and return differences every time you update your lists. I mean, something like the following image.

To compare 2 columns and return differences every time I add new items, I can follow 2 ways.

**Way 1: **

Can you remember the **FILTER-COUNTIF** formula (check the 2nd section)? It was like: **=FILTER(B6:B15,COUNTIF(C6:C15,B6:B15)=0)**

Here **B6:B15** and **C6:C15** were for List-1 and List-2.

I will make these ranges dynamic using the following formulas.

Use **=$B$6:INDEX($B:$B,COUNTA($B:$B)+ROW($B$5)-1)** instead of **B6:B15** in the formulas.

And **=$C$6:INDEX($C:$C,COUNTA($C:$C)+ROW($C$5)-1)** instead of **C6:C15**.

So, the **FILTER-COUNTIF** formula will have the following look now.

`=FILTER($B$6:INDEX($B:$B,COUNTA($B:$B)+ROW($B$5)-1),(COUNTIF($C$6:INDEX($C:$C,COUNTA($C:$C)+ROW($C$5)-1),$B$6:INDEX($B:$B,COUNTA($B:$B)+ROW($B$5)-1))=0)*($B$6:INDEX($B:$B,COUNTA($B:$B)+ROW($B$5)-1)<>""))`

If you use this formula in cell **E6**, and new items in the lists, it will work forever comparing and returning differences from List-1.

**Way 2:**

This is nothing different from the first 1. We will just make the formula look easier. We will use the **Named Range** for the dynamic ranges to define names and that’s all.

The formula will look much more legible.

For that, go to **Formulas **tab ⇒ **Define Name** ⇒ **New Name** dialog box ⇒ **Refers to** field.

Now input the following formula.

`='2.2 Dynamic (Named_Range)'!$B$6:INDEX('2.2 Dynamic (Named_Range)'!$B:$B,COUNTA('2.2 Dynamic (Named_Range)'!$B:$B)+ROW('2.2 Dynamic (Named_Range)'!$B$5)-1)`

Name it as **List_1**. Then click **OK**.

**Note**

*Replace “2.2 Dynamic (Named_Range)” with your respective sheet name. Or when you select the ranges, this will be automatically selected.**You must add an underscore between words while naming formulas. Also, Press***F2**if you want to edit anything in the**Refers to**field.

Similarly, name the List-2 items using the following formula and name it **List_2**.

`='2.2 Dynamic (Named_Range)'!$C$6:INDEX('2.2 Dynamic (Named_Range)'!$C:$C,COUNTA('2.2 Dynamic (Named_Range)'!$C:$C)+ROW('2.2 Dynamic (Named_Range)'!$C$5)-1)`

Now, the formula will look like this.

`=FILTER(List_1,(COUNTIF(List_2,List_1)=0)*(List_1<>""))`

Now, add a new name in List-1 and see what happens!

## How to Compare Two Datasets with Multiple Columns and Return Whole Rows for Differences

Assume, we have two separate datasets such as the Current List and the New List. We need to find the mismatched data of the New List and gather differences of multiple Excel columns with a combination of **FILTER**, **NOT**, **XMATCH**, and **ISNUMBER **functions.

- Now, input the formula as follows in the
**B21**and hit**Enter**. You will get the outcome in an array format.

`=FILTER(B14:D18,(NOT(ISNUMBER(XMATCH(B14:B18,B6:B9)))*(B14:B18<>"")))`

## How to Compare Two Lists and Highlight Differences with Excel Conditional Formatting

In this section, we will learn 2 approaches to compare two lists and return differences using the **Conditional Formatting **tool. The selection of the **Unique **option and application of the **New Formatting Rule**, are both applicable to mark mismatched values.

__Method 1: Use Highlight Cell Rules to Mark Differences__

The unique values in conditional formatting represent the uncommon values between the two lists.

**Follow these steps:**

Select the range **B6:C15** and click as follows: **Home **=> **Highlight Cells Rules** => **Duplicate Values**.

- Thus, the
**Duplicate Values**dialog box shows up. Then select**Unique**=>**OK**.

- As a result, all the uncommon names of
**List-1**and**List-2**get highlighted.

__Method 2: Mark Differences with New Formatting Rule__

Another alternative to compare two lists, return differences, and highlight the unmatched values is the application of the **New Formatting Rule**.

- To get the
**New Formatting Rule**dialog box, select the range**B6:B15**and click as follows:**Home**=>**Conditional Formatting**=>**New Rule**.

- Thus the
**New Formatting Rule**dialog box appears. - Now select
**Use a formula to determine which cells to format option**from**Select a Rule Type** - Then insert the following formula => select
**Preview**=>**OK**.

`=COUNTIF($C$6:$C$15, B6) = 0`

- Finally, all the uncommon values are highlighted in
**List-1**.

- Similarly selecting the
**C6:C15**range and applying the following formula, you can highlight the unmatched names of**List-2**.

`=COUNTIF($B$6:$B$15, C6) = 0`

**Download Practice Workbook**

The first Excel file contains all the examples except the VBA code.

The following Excel file contains the macro example.

*This is how you can compare two lists and return differences in Excel. You can simply use the IF–COUNTIF and INDEX–MATCH formulas but these don’t return the results in array format and you are required to use the Fill Handle tool. On the other hand, the IF–ISNA–MATCH, and FILTER–COUNTIF formulas return an array format (but they are available in more updated versions of Excel). Unlike other approaches, the TEXTJOIN–IF–MATCH formula returns differences in a single cell joined by a delimiter. We also developed a formula for two dynamic lists combining FILTER, INDEX, COUNTA, COUNTIF, and ROW functions. Marking the differences highlighted can be done by using the Conditional Formatting tool. We have added a VBA code that is applicable to compare any two lists and return differences in a suitable cell location.*

*Please leave your insights, queries, and suggestions in the comment box.*

**<< Go Back to Columns | Compare | Learn Excel**