# Excel Compare Two Lists and Return Differences (7 Ways) 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.

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

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. ### 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. ### 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. 1. Go to the Home
2. Then select the Conditional Formatting
3. Select the New Rule Step 3: Now again follow the sequences.

1. Select the marked option first.
2. Enter the below formula in the marked box.
`=ISNA(VLOOKUP(A5,\$D\$5:\$D\$15,1,FALSE))`
3. You can format the colors as per your choice.
4. Then click on the Ok. Step 4: Now observe the output. ## 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.  