Sometimes we need to compare two lists in Excel for various purposes. In Excel, the list can be presented in two ways. It can be column-wise or row-wise. MS Excel offers many choices to compare and match data, but most of them focus on searching in one column. In this tutorial, we will explore numerous techniques to compare two lists and return the differences in Excel.

**Table of Contents**hide

**Download Practice Workbook**

**4 Ways to Compare Two Lists and Return Differences in Excel**

This section covers **4** distinct situations showing ways to compare two lists and return differences in Excel. Let’s demonstrate them one by one.

**1. Compare Two Lists for Differences in Same Row (Appropriate Match)**

Let’s say, we have a dataset of two individual orders. In our dataset, we have two tables for the orders. Now our task is to find out the products which are in two order tables.

We will return a message if the product’s name is matched or not for each row.

For the formula, we will use the **IF function**. The *syntax* of the function is:

`IF (logical_condition, [value_if_true], [value_if_false])`

In the first portion it takes the condition or criteria, then the value which will be printed if the result is true and then if the result is false.

**Logical_condition -> **It is the logical condition that will be followed to make the decision.

**[value_if_true] -> **This is the message which will be printed if the decision is true.

**[value_if_false] -> **This is the message which will be printed if the decision is false.

**Steps**:

- First of all, type one of the formulas in cell
**G5**.

`=IF(B6=E6,"Matched","Not Matched")`

Or

`=IF(B6<>E6, "Not Matched", "Matched")`

**Formula Explanation**

Here I have shown two ways to use the **IF** function in the formula. We can either use the equal (=) sign or the not equal sign (<>) in our logical condition. Based on that our output may vary.

- Then, drag the
**Fill Handle**tool to**Autofill**the formula up to cell**H16**and the cells will differentiate between the two tables whether they match or not.

**2. Compare Two Lists for Differences in Same Row (Exact Match)**

In the previous method, we ignored the case sensitivity issue. Now here we will see the comparison where we will consider a case-sensitive circumstance. Here, we will use the **EXACT **function nested in the **IF **function to find the exact match between the two tables whereas the previous method deals with an appropriate match.

The *syntax *of the **EXACT **function is:

`EXACT (text1, text2)`

This function can take two arguments in its parameter and compare them. As we can see it takes two arguments which will be string or text and return the compared result.

Again, our dataset will be the same as above.

- At first, apply the formula below in cell
**H6**.

`=IF(EXACT(B6, E6), "Matched ", "Not Matched")`

**Formula Explanation**

Here additionally the **EXACT **function has been used to get the exact match in both lists.

In the formula, the **EXACT **function denotes the logical condition for the **IF** function as our concern is to find the same match. As cells **B6 **and **E6 **don’t match, so the output=> **Not Matched**

- Now, copy down the formula up to
**H16**and observe the output.**3**pairs from the two tables have been matched.

**3. One Item in Another Table**

Now let’s assume we have the two tables (**Table 1** and **Table 2**) in the dataset and we will find out whether any product from **Table 1** exists in **Table 2** or not and show it in the **Remarks **column.

**3.1. Combining IF and COUNTIF Functions**

Here, the **COUNTIF **function will be used with the **IF **function.

- First, apply the following formula in cell
**H6**.

`=IF(COUNTIF($E:$E,$B6)=0,"No match in Table 2","Match in Table 2")`

You can also apply any of the following formulas with **ISERROR**, **MATCH**,** SUM **functions:

`=IF(ISERROR(MATCH($B6,$E$5:$E$16,0)),"No match in Table 2","Match in Table 2")`

Or

`=IF(SUM(--($E$6:$E$16=$B6))=0, "No match in Table 2", "Match in Table 2")`

**Formula Explanation**

Here additionally the **COUNTIF **function is applied. **COUNTIF($E:$E,$B6)=0 **This portion finds out the matching data by comparing one row from the with all data in **Table 2**. If any of the data in **Table 2** is matched with the specific row in **Table 1** then the **COUNTIF **function returns some value except** 0** and prints **No match in Table 2. **If the function returns **0** then it will print **Match in Table 2**.

- Copy down the formula up to
**H16**. The differences (i.e.**No Match in Table 2**) are shown in the image below.

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

**3.2. Using VLOOKUP Function**

Now let’s see how we can use the **VLOOKUP** and **ISNA **functions for the dataset same as **Method 3.1**.

Here, all you have to do is to apply the formula in cell **H6** and drag it down for other cells.

`=IF(ISNA(VLOOKUP(B6,$E$6:$E$16,1,FALSE)),"No match in Table 2","Match in Table 2")`

**Formula Explanation**

**ISNA(VLOOKUP(B6,$E$6:$E$16,1, FALSE)) **this part of the formula finds the logical value which could be **True **or **False**. According to the logical output, the **IF** function will return a message.

See the differences in the image below.

**Read More:** **VLOOKUP Formula to Compare Two Columns in Different Sheets!**

**Similar Readings**:

**Macro to Compare Two Columns in Excel and Highlight Differences****Excel Macro to Compare Two Columns (4 Easy Ways)****Excel Formula to Compare and Return Value from Two Columns (5 Formulas)****How to Compare Two Columns and Return Common Values in Excel**

**4. Highlight Differences Applying Conditional Formatting**

In this method, we will apply **Conditional Formatting** to compare two lists and highlight their differences. For this, we will use the same dataset and formula which was used in the earlier method.

Here instead of using printing messages, we will highlight rows to differentiate between data.

**Steps**:

- First, select the cells that we need to highlight. If the data range is not adjacent, select them by pressing the
**CTRL**button.

- Then, go to the
**Home**tab> click dropdown of**Conditional Formatting**> choose**Highlight Cells Rules**> select**Duplicate Values…**

- Now, the
**Duplicate Values**dialog box will appear. Here,**Duplicate**is selected by default and your cells with duplicate values will be highlighted. - But we don’t want the similarities, rather we want to find out the differences. So, select
**Unique**from the dropdown so that you can find the differences between the tables highlighting the unique values. Click**OK**.

- Hence, all the
**Unique**values will be highlighted and you will find the differences between the lists.

**Read More:** **How to Compare Two Columns in Excel For Finding Differences**

## How to Find Texts Occurring More Than Twice in Excel

Now let’s find out the names of products in **Table 1** which occurred more than twice in **Table 2**.

Apply the following formula in cell **H6** and drag it down.

`=IF(COUNTIF($E$6:$E$16,$B6)>2,"Yes","No")`

**Formula Explanation**

Here **COUNTIF($E$6:$E$16,$B6)>2 **this portion finds out the rows if any product in **Table 1** has appeared more than two times in **Table 2**. Depending on this result **IF **function decides whether to print **Yes** or **No **as messages.

The text in **Table 1 **occurring more than twice in **Table 2 **will show **Yes **in the output section and vice versa.

**Read More:** **Excel Count Matches in Two Columns (4 Easy Ways)**

## Compare Two Lists in Excel and Extract Matching Data

Let’s say, we have two tables one is the Product Table with Product Name and Prices, another is Order Table with Order ID, Products, and Prices. Now our task is to copy down the prices in the order list by comparing the product’s name.

Apply the formula in cell** G6** and then drag it down for other cells.

`=INDEX($C$6:$C$12,MATCH($F6,$B$6:$B$12,0))`

**Formula Explanation**

Here in the formula, we have used two functions which are **INDEX **and **MATCH**. **MATCH($F6,$B$6:$B$12,0) **this part finds the matched rows by comparing the product names. Then using the **INDEX **function the prices are extracted from Product Table to Order Table.

## Practice Section

I am providing a practice sheet to the workbook for your practice purpose.

**Conclusion **

These are the ways to compare two lists and return differences in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.