Learn how to compare two lists in Excel using user interface commands, formulas, and advanced Microsoft Excel data analytics tools.

If you need to identify duplicates, match data across lists, or highlight changes over time in Excel for a small dataset, say 5 to 10 rows, you might do that manually using the naked eye. How about thousands of rows of data? Here you must use Excel functions, built-in commands, or Excel automation tools.

Find below some of the proven ways to compare two lists in Excel for matches and differences.

Using Conditional Formatting

Applying the built-in or formula-based Conditional Formatting rules on one or two columns being compared is the easiest method to highlight similar and dissimilar items in two lists.

Find below three different techniques that involve user interface commands and formulas:

Highlight Duplicates

Select your dataset that contains two lists of items separated into two columns, for example, columns A and B. In the present exercise, it’s the A2:B10 range.

Click on the Conditional Formatting drop-down list from the Home tab and hover the cursor over the Highlight Cells Rules menu.

Select the Duplicate Values option in the overflow menu on the right side.

Excel will highlight the duplicate values in a default light red cell fill formatting.

The Duplicate Values dialog box will also pop up.

You can use it to switch to Unique and Duplicate values by clicking the Format cells that contain drop-down list.

You can also use the values with drop-down menu to customize the formatting style.

📒 Read More: 8 Ways To Fill Every Other Row in Microsoft Excel

Highlight Items in List 1, Not in List 2

Often, the datasets you’re comparing are disorganized or require custom rules. Here, the formula-based Conditional Formatting rule steps in.

For instance, you want to highlight items that are only in List 1, which is column A.

Select the content of column A and click on the New Rule option in the Conditional Formatting menu.

Click on the Use a formula… rule inside the New Formatting Rule dialog.

Enter the following formula into the Format values where… field.

=COUNTIF($B$2:$B$10, A2) = 0

In the above formula, change the reference cell range B$2:$B$10 according to the cell range of the second list or column you’re comparing to the first list or column. Here, it’s column B.

Click the Format button and use the Fill, Border, Font, and Number tabs to create a custom format for the highlighted entries.

Check the Preview section to confirm if your selected formatting style is reflecting or not.

Click OK to apply the rule.

Excel will fill only those cells that contain unique entries in List 1.

Highlight Items in List 2, Not in List 1

You can follow the same steps mentioned above to highlight items in List 2, that are not in List 1.

Simply, select List 2 or column B this time and use the following formula for creating a Conditional Formula rule:

=COUNTIF($A$2:$A$10, B2) = 0

Find above a sample dataset showing differences in List 2, column B, when compared to List 1, column A, using a formula-based Conditional Formatting rule.

Using Go To Special

Go To Special tool lets you compare two columns or lists of data using only a few clicks. However, you must select the dataset carefully. For example, if you wish to differentiate List 2 from List 1, you must choose from the List 1 side.

Let’s say you want to find out the unique values in column B (List 2) in the above dataset compared to column A (List 1).

So, select the entire dataset by clicking the cell A1.

Press Ctrl + G to bring up the Go To dialog box.

Click on the Special button.

Select the Row differences option on the Go To Special dialog and click OK.

Excel will highlight the differences temporarily. If you click any other cell now, the identified cells will be unselected.

So, click on the Fill Color command in the Font block of the Home tab and choose a cell fill color.

Congratulation! You’ve successfully compared two lists in Excel.

To highlight the differences in column A or List 1, select the entire dataset by clicking B1.

Now, repeat the same steps as outlined above.

Using COUNTIF Function

You can use the COUNTIF function in a helper column to identify rows in List 1 and List 2 that have the same entries or values.

Create a Comparison column adjacent to List 2 in your worksheet as shown in the sample above.

Select C2 or whichever is the first cell in the Comparison column and enter the following formula into it:

=IF(COUNTIF(B$2:B$10, A2)=0, "Not Found", "Found")

Hit Enter to calculate the cell.

In this formula, you’ll compare List 1 with List 2 and populate Found when there’s a matching value in the same row and Not Found when there’s a mismatch. Change the cell range reference, B$2:B$10, according to the cell range address of the List 2 table or data.

Now, select C2 and drag down the fill handle to replicate the formula in the rest of the cells.

So, now you’ve got a comparison column indicating matches and mismatches in the two input lists.

Using MATCH Function

The MATCH function finds the location of the queried item in a cell range and generates a value that corresponds to the row number relative to the selected array. You can nest the MATCH function within IF and ISNUMBER to generate Match or Not Match for an effective comparison of two lists in Excel.

Create a Helper column to the right side of List 2 as shown in the above example dataset.

Let’s say, you want to compare List 1 with List 2 and show Match when values are the same and No Match when values differ.

So, select C2 or the first cell of your Helper column and type in this formula:

=IF(ISNUMBER(MATCH(A2, B$2:B$8, 0)), "Match", "No Match")

In the above formula, A2 is the List 1 item to be looked up in List 2 and B$2:B$10 is the cell reference for List 2. Modify these entries according to your own dataset.

The numeric 0 tells the Excell MATCH function to get a complete match before fetching the position of the queried item.

Hit Enter to calculate the cell.

Select this cell and drag down the fill handle to copy the formula into the rest of the cells.

The above dataset compares Lists 1 and 2 in the Helper column. This Helper column shows matched and not-matched rows.

Using XLOOKUP Function

You can also use the XLOOKUP function in Excel to compare two lists. It either returns the matching value or displays an error message, such as Not Found. This allows you to easily identify which items from one list have corresponding matches in the other.

Suppose, you’ve got emails of leads in one worksheet and emails from which you’ve got responses in another worksheet. Before sending a reminder or promotional email to the non-responding leads, you might want to cancel out those who already responded.

So, go to the worksheet containing leads and create the Reminder column on the right side of the lead emails column.

In the first cell of the Reminder column, enter the following formula:

=XLOOKUP(A2, Sheet6!$A$2:$A$7, Sheet6!$A$2:$A$7, "Not Found") 

In the above formula, A2 is the first cell of the leads column. Sheet6!$A$2:$A$7 corresponds to the list of emails from which you’ve got replies.

Use this range twice, as the lookup and return array. Not Found is the default message to show if the queried value isn’t available in the referred cell range: Sheet6!$A$2:$A$7. You must customize these argument values according to your own worksheets.

Hit Enter to calculate the cell.

Use the fill handle of the first cell and drag it down to copy the formula into the rest of the cells.

Rows that show Not Found mean the value in the corresponding cell to the left isn’t available in the referred list in another worksheet.

Using Power Query

Power Query is best suited for the comparison of lists in Excel when working with a large dataset and importing data from an external database server. Also, it allows you to compare two lists from different viewpoints, like all from the first list, all from the second list, only matching rows, and so on.

To import an external dataset, go to the Data tab and click on the Get Data drop-down menu. Then, you can choose your sources, like From Database > From SQL Server database, From Azure > From Azure SQL Database, etc.

If the lists you want to compare are in a worksheet, select the first list and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog box.

Go to the Power Query File tab and select Close and Load To.

On the Import Data dialog, select Only Create Connection.

Click OK to close Import Data.

Follow the same steps for the second list.

The Queries & Connections navigation panel will open on the right side.

There, you should see two tables below the Queries column.

Now, click on the Get Data command again and choose Merge from the Combine Queries sub-menu.

You should see the Merge wizard.

Click on the first drop-down menu and choose the first list, like Table1 (3) in this exercise.

Now, click the second drop-down menu and select the second list.

Click both of the added lists to activate those.

Click on the Join Kind drop-down menu and select an option, like Inner.

The matching rows from two input lists will form a table and open in the Power Query Editor.

Delete the redundant blank table named Table3.

Import the remaining table to the same worksheet where the input lists are.

You’ll now have a comparison of both the lists in the new table imported from Power Query that shows only the matching entries.

To visualize items that are unique in the first list compared to the second, you can use the Join Kind option Left Anti in the Merge wizard.

Excel will create a new table and export that to the Power Query Editor. You can import that to the existing or new worksheet.

Similarly, to list unique entries from the second table, select the Right Anti Join Kind in the Merge wizard.

When the table is loaded in Power Query, click on the Expand icon for Table3, choose Expand, and select OK.

Remove the first column that shows null values.

Now, you can export the remaining table to your worksheet to show the unique entries from the second list compared to the first.

Using Excel VBA

This Excel VBA-based approach allows you to fully automate the list comparing task. If you didn’t script previously, follow the steps outlined here.

Before moving further, read this tutorial first to learn the prerequisite steps:

📒 Read More: How To Use The VBA Code You Find Online

Now, use this script to create a macro:

Sub CompareLists()
    Dim ws As Worksheet
    Dim list1 As Range, list2 As Range
    Dim uniqueList1 As Collection, uniqueList2 As Collection, matchingList As Collection
    Dim cell As Range
    Dim outputRange As Range

    ' Select the first list
    Set list1 = Application.InputBox("Select the first list", Type:=8)
    ' Select the second list
    Set list2 = Application.InputBox("Select the second list", Type:=8)

    ' Initialize collections
    Set uniqueList1 = New Collection
    Set uniqueList2 = New Collection
    Set matchingList = New Collection

    ' Find unique entries in list1
    On Error Resume Next
    For Each cell In list1
        If IsError(Application.Match(cell.Value, list2, 0)) Then
            uniqueList1.Add cell.Value, CStr(cell.Value)
        Else
            matchingList.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0

    ' Find unique entries in list2
    On Error Resume Next
    For Each cell In list2
        If IsError(Application.Match(cell.Value, list1, 0)) Then
            uniqueList2.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0

    ' Output unique entries in list1
    Set outputRange = Application.InputBox("Select where to output unique entries from list 1", Type:=8)
    For i = 1 To uniqueList1.Count
        outputRange.Cells(i, 1).Value = uniqueList1(i)
    Next i

    ' Output unique entries in list2
    Set outputRange = Application.InputBox("Select where to output unique entries from list 2", Type:=8)
    For i = 1 To uniqueList2.Count
        outputRange.Cells(i, 1).Value = uniqueList2(i)
    Next i

    ' Output matching entries
    Set outputRange = Application.InputBox("Select where to output matching entries", Type:=8)
    For i = 1 To matchingList.Count
        outputRange.Cells(i, 1).Value = matchingList(i)
    Next i

    MsgBox "Comparison complete!"
End Sub

Press Alt + F8 to run the macro. Select the CompareLists macro and hit Run.

The macro will show a series of pop-ups as listed below:

• Select List 1

• Choose List 2

• Select destination for unique entries from List 1

• Choose cell range for unique values from List 2

• Select a destination for matching values from both lists

Find above a neat comparison of two lists using a VBA macro.

Conclusions

Now you know how to compare two lists in Excel. You’ve learned and practiced several methods involving simple user interface commands like Go To Special and Conditional Formatting. Also, you’ve learned the Excel functions you can use.

Finally, you’ve also mastered the techniques to compare two lists in Excel for matches and differences using advanced tools like Power Query and Excel VBA.

Which one do you prefer the most? Do you know of a better method that I didn’t mention? Comment below to share your feedback and suggestions.