Compare Two Lists in Excel

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

How to Compare Two Lists in Excel? (Top 6 Methods)

Below are the six different methods used to compare two lists of a column in Excel for matches and differences.

  1. Method 1: Compare Two Lists Using Equal Sign Operator
  2. Method 2: Match Data by Using the Row Difference Technique
  3. Method 3: Match Row Difference by Using the IF Condition
  4. Method 4: Match Data Even If There is a Row Difference
  5. Method 5: Highlight All the Matching Data using Conditional Formatting
  6. Method 6: Partial Matching Technique

Now, let us discuss each of the methods in detail with an example: –

You can download this Compare Two Lists Excel Template here – Compare Two Lists Excel Template

#1 Compare Two Lists Using Equal Sign Operator

We must follow the below steps to compare the two lists.

  1. Immediately after the two columns, we must insert a new column called “Status” in the next column.


    Compare Two Lists in Excel Example 1.1

  2. Now, we must put the formula in cell C2 as =A2=B2.


    Compare Two Lists in Excel Example 1.2

  3. This formula tests whether the cell A2 value is equal to cell B2. If both cell values are matched, we will get TRUE or FALSE results.


    Compare Two Lists in Excel Example 1.3

  4. We will drag the formula to cell C9 to determine the other values.


    Compare Two Lists in Excel Example 1.4.0

Wherever we have the same values in common rows, we get the result as “TRUE” or “FALSE.”

#2 Match Data by Using Row Difference Technique

You might not have used the “Row Difference” technique at your workplace. But today, we will show you how to use this technique to match data row by row.

  • Step 1: To highlight non-matching cells row by row, we must select the entire data first.
Example 2
Compare Two Lists in Excel Example 2.1
  • Step 3: Press the “F5” key to open this window. In the “Go To” window, press the “Special” tab.
Example 2.2.0
  • Step 4: In the next window, we must go to the “Go To Special” and choose the “Row differences” option. Then, click on “OK.”
Compare Two Lists in Excel Example 2.3.0

We will get the following result.

Example 2.4

As shown in the above window, it has selected the cells wherever there is a row difference. Therefore, we must fill in some colors to highlight the row difference values.

Compare Two Lists in Excel Example 2.5

#3 Match Row Difference by Using IF Condition

How can we leave out the IF condition when we want to match data row by row? In the first example, we have either “TRUE” or “FALSE.” But what if we need a different result instead of the default results of either “TRUE or FALSE.” Assume we need a result as “Matching” if there is no row difference, and the result should be “Not Matching” if there is a row difference.

  • Step 1: First, we must open the IF condition in cell C2.
Example 3
Compare Two Lists in Excel Example 3.1
  • Step 3: We must enter the result criteria if the logical test is “TRUE.” In this scenario, the result criteria are “Matching,” If the row does not match, we need the result as “Not Matching.”
Example 3.2
  • Step 4: Next, we need to apply the formula to get the result.
Compare Two Lists in Excel Example 3.3
  • Step 5: We must drag the formula to cell C9 to determine the other values.
Compare Two Lists in Excel Example 3.4.0

#4 Match Data Even If There is a Row Difference

The matching data on the row differences method may not always work; the value may be in other cells too. So we need to use different technologies in these scenarios.

Now, look at the below data.

Compare Two Lists in Excel Example 4

In the above image, we have two lists of numbers. We need to compare list 2 wish list 1. So let us use our favorite function VLOOKUPFunction VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

Example 4.1.0

So, if the data matches, we get the number; otherwise, we get the error value as #N/A.

Showing error values does not look good. So instead of showing the error, let us replace them with the word “Not Available.” For this, use the IFERROR function in ExcelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more.

Compare Two Lists in Excel Example 4.2

#5 Highlight All the Matching Data

If you are not a fan of Excel formulasExcel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more, do not worry. We can still match data without the formula. For example, using simple conditional formatting in ExcelConditional Formatting In ExcelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more, we can highlight all the matching data of two lists.

  • Step 1: We must first select the data.
Example 5
  • Step 2: Now, we must go to “Conditional Formatting” and choose “Highlight Cell Rules” >> “Duplicate Values.”
Compare Two Lists in Excel Example 5.1
  • Step 3: As a result, we can see the “Duplicate Cell Values” formatting window.
Example 5.2
Compare Two Lists in Excel Example 5.3
  • Step 5: This will highlight all the matching data from the two lists.
Example 5.4
  • Step 6: Just in case, instead of highlighting all the matching data, if we want to highlight not matching data, then we can go to the “Duplicate Values” window and choose the option “Unique.”
Example 5.5

As a result, it will highlight all the non-matching values, as shown below.

Compare Two Lists in Excel Example 5.6

#6 Partial Matching Technique

We have seen the issue of not having full or the same data in two lists. For example, if the List 1 data has “ABC Pvt Ltd. In List 2, we have “ABC” only. In these cases, all our default formulas and tools are not recognized. Therefore, we need to employ the special character asterisk (*) to match partial values in these cases.

Example 6

In List 1, we have the company name and revenue details. In List 2, we have company names but not exact values as in List 1. It is a tricky situation that we all have faced at our workplace.

In such cases, still, we can match data by using a special character asterisk (*).

Compare Two Lists in Excel Example 6.1

We get the following result.

Example 6.2

We will drag the formula to cell E9 to determine the other values.

Compare Two Lists in Excel Example 6.3.0

The wildcard character asterisk (*) was used to represent any number of characters so that it will match the full character for the word “ABC” as “ABC Pvt Ltd.”

Things to Remember

  • The use of the above techniques for comparing two lists in Excel upon the data structure.
  • If the data is not organized, row-by-row matching is not the best suited.
  • VLOOKUP is the often-used formula to match values.

Recommended Articles

This article is a guide to Compare Two Lists in Excel. We discuss the top 6 methods to compare two columns list in Excel for the match, along with examples and a downloadable Excel template. You may learn more about Excel from the following articles: –