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 the Practice WorkBook

## 7 Ways to Compare Two Lists and Return Differences in Excel

### 1. Compare Two List for Differences in the Same Row (case insensitive)

Letâ€™s imagine 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. Letâ€™s see the syntax of the function.

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

**Step 1: **Enter the formula in cell **G5**.

`=IF(A5=D5,"Matched","Not Matched")`

Or

`=IF(A5<>D5, "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.

**Step 2: **Copy down the formula up to **G15**.

### 2. Compare Two List for Differences in the Same Row (case sensitive)

In the previous method, we were ignoring the case-sensitivity issue. Now here we will see the comparison where we will consider case sensitivity. Again, our dataset will be the same as above.

**Step 1: **Enter the formula in cell **G5**.

`=IF(EXACT(A5, D5), "Matched ", "Not Matched")`

**Formula Explanation**

Here additionally I have used the **EXACT **function to get the exact match in both lists. Letâ€™s see the fundamentals of the function.

`EXACT (text1, text2)`

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

In the formula, the **EXACT **function is used as the logical condition as our concern is to find the same match.

**Step 2: **Copy down the formula up to **G15** and observe the output.

### 3. Compare Two Lists in Excel for Differences

Now letâ€™s assume we have the two tables of orders (Order 1 and Order 2)Â in the dataset and we will find out whether any product from the order 1 table is in the order 2 table or not.Â

**Step 1:** Enter the formula in cell **G5**.

`=IF(COUNTIF($D:$D,$A5)=0,"No match in Order 2 Table","Match in Order 2 Table ")`

Or

`=IF(ISERROR(MATCH($A5,$D$5:$D$15,0)),"No match in Order 2 Table","Match in Order 2 Table ")`

Or

`=IF(SUM(--($D$5:$D$15=$A5))=0, "No match in Order 2 Table", "Match in Order 2 Table ")`

**Formula Explanation**

Here additionally the COUNTIF function is applied. **COUNTIF($D:$D,$A5)=0 **This portion finds out the matching data by comparing one row from the **order 1 **table with all data in **order 2** table. If any of the data in order 2 table is matched with the specific row in order 1 table then COUNTIF function returns some value except 0 and prints **No match in Order 2 Table. **If the function returns 0 then it will print **Match in Order 2 Table**. For more details about the **COUNTIF **function, you can check another article about this function at this Link or Link:

**Step 2: **Copy down the formula up to **G15**.

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

### 4. Compare Two Lists using VLOOKUP

Now letâ€™s see how we can use the VLOOKUP function for the previous method. Here our dataset will be the same as method 3.

**Step 1:** Enter the formula in cell **G5**.

`=IF(ISNA(VLOOKUP(A5,$D$5:$D$15,1,FALSE)),"No match in Order 2 Table","Match in Order 2 Table ")`

**Formula Explanation**

In the formula, we have used another popular excel function which is the VLOOKUP** **functionÂ

You can check the article by clicking on the link to get more information about **VLOOKUP** for logical conditions.Â

**ISNA(VLOOKUP(A5,$D$5:$D$15,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.Â

**Step 2: **Copy down the formula up to **G15**.

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

### 5. Compare Two Lists and Find Names That Occur More Than Twice

Now letâ€™s find out the names of products in the order 1 table which occurred more than twice in the order 2 table.

**Step 1: **Enter the formula in cell **G5**.

`=IF(COUNTIF($D$5:$D$15,$A5)>2,"Yes","No")`

**Formula Explanation**

Here **COUNTIF($D$5:$D$15,$A5)>2 **this portion finds out the rows if any product in order 1 table has appeared more than two times in order 2 table. Depending on this result **IF **function decides whether to print Yes or No as messages.

**Step 2: **Copy down the formula up to **G15**.Â

### 6. Compare Two Lists in Excel and Pull Matching Data

Letâ€™s think 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.

**Step 1: **Enter the formula in cell** F5**.

`=INDEX($B$5:$B$11,MATCH($E5,$A$5:$A$11,0))`

**Formula Explanation**

Here in the formula, we have used two functions which are **INDEX **and **MATCH**. **MATCH($E5,$A$5:$A$11,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.

**Step 2: **Copy down the formula up to **F14**.

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

### 7. Compare Two Lists and Highlight Differences

In this method, we will see how to compare two lists and highlight their differences. For this, we will use the same dataset and formula which was used in method 4. But here instead of using printing messages, we will highlight rows.Â

**Step 1: **Select the cells that we need to highlight. Here I have selected the products in order 1 table.

**Step 2: **Follow the procedures.

- Go to the
**Home** - Then select the
**Conditional Formatting** - Select the
**New Rule**

**Step 3: **Now again follow the sequences.

- Select the marked option first.
- Enter the below formula in the marked box.
`=ISNA(VLOOKUP(A5,$D$5:$D$15,1,FALSE))`

- You can format the colors as per your choice.
- Then click on the
**Ok.**

**Step 4: **Now observe the output.

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

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