Md. Abdullah Al Murad
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.
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: How to Use VLOOKUP Function to Compare Two Lists in Excel
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
- VLOOKUP Formula to Compare Two Columns in Different Sheets!
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.
