Excel Compare Two Lists and Return Differences (7 Ways)

Copy down the formula up to G15

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

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

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.

Enter the formula using IF function

Step 2: Copy down the formula up to G15.

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.

Enter the formula using IF and Exact function

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

Copy down the formula up to G15

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. 

Compare Two Lists in Excel Differences

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:

 Enter formula using Countif and IF function

Step 2: Copy down the formula up to G15.

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 

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. 

Enter the formula using VLOOKUP function

Step 2: Copy down the formula up to G15.

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.

 Enter the formula using if and countif function

Step 2: Copy down the formula up to G15

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.

Compare Two Lists in Excel and Pull Matching Data

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.

enter formula using INDEX and IF function

Step 2: Copy down the formula up to F14.

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.

Select the data

Step 2: Follow the procedures.

  1. Go to the Home
  2. Then select the Conditional Formatting
  3. Select the New Rule
    create a 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.
    create new formatting rule

Step 4: Now observe the output.

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

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo