Home > Formulas > Count > Compare

Compare Two Excel Lists Find New Items

Compare two Excel lists, to find new items in the second list, and add them to the first list. My video shows the steps, and there are written notes and a sample file to download.

Note: To check for exact matches in a list, including upper and lower case, go to the Compare Cell Values page. There are EXACT function examples, written steps, a video, and sample file.

Author: Debra Dalgleish

COUNTIF formula results 1 or zero

Video: Compare Two Excel Lists
Two Lists in Workbook
Create Excel Table
Check for New Records

Sort Formula Results
Copy New Records to Main List
Compare Lists - FILTER Function
Get Sample Files

Video: Compare Two Excel Lists

In this video, see how to compare two lists in Microsoft Excel using formulas to see if they have the same entries. Then find all the new items from List 2, and add them to the main list, without creating duplicate values. The written steps, and the video transcript, are below the video.

Video Timeline

00:00 Introduction
00:15 Two Lists
00:45 Excel Table
01:30 Find New Records
02:42 Add New Records
03:30 Get the Sample File

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

✅ Click here for Transcript

Video Transcript: Compare Two Excel Lists

Here is the full transcript for the Compare Tow Excel Lists video.

Introduction

We've got a list of names and email addresses. And we just got a new list that has some of those same names and some new ones.

We're going to find the new ones and get them into our list. This is Debra Dalgleish from Contextures.com.

Two Lists

Here's our old list. It's in a named Excel table. We've got email, last name, first name, and whether or not this person will attend our conference.

The email is unique. No two people are going to have that same email address.

We've got a new list, which also has email. And first and last names.

We're going to check the emails and see if they're already in our list. And that will keep us from getting duplicates.

Excel Table

Before I start to work with this list, I'm going to change it to a named Excel table, which will make it easier to select things.

So select any cell in the list.
On the Home tab, click Format as Table and choose a style you like. So I'll go with a black heading.
Where is the data? It's selected the correct range.
My table does have headers, so I'll leave that checked, and click OK.

If any cell in the table is selected, there's a table Design tab on the ribbon.

If I click that, I can see that this was given a default name.
I'm going to change this to NewTable.

So going back to this table, this is called Attendees

Find New Records

To find the new items in this list, I'm going to check the emails and see if they're already in the Attendees table.

And to do that, I'm going to add a column here and I'll type a heading here, InList. Is this email in that other list?

And to do that, I'm going to do a count, I'll count the number of times this email address is in the Attendees table.

And the function that will do that for us is COUNTIF

I'll start by typing =COUNTIF, an open bracket.
What it wants is a range.
- So it's going to check somewhere, and where we want it to check is the email column in our Attendees table, so I'll go there
.And when I click at the top of that column, it's got the table name and the column name, and that's what we want it to check
Type a comma
Going back to the new list, what we want it to check is this email cell in each row.
So when I click on that, now it's showing the field name in this table, and that's what it's going to check
Close the bracket and press Enter.

Add New Records

In this column, the formula either shows a one or a zero.

If it found the email address, it's a one.
If it did not find it, we have a zero.

We want to copy anything that has a zero.

And to make that easy, I'm going to sort the column and then select all the zeros.

To sort, click the arrow in the heading and sort smallest to largest.

So all the zeros are at the top where we can easily copy them

Drag to select
Don't copy that formula column
Select all the names
Then Ctrl + C
Then go to the old list
Go to the bottom of the list, end and down arrow.
And then one more down arrow
And press Ctrl + V to paste.

And now all the new names are in the list.

Two Lists in Workbook

We need to compare two Excel lists (shown below), and update the Main List. In the sections below, you'll see the steps to finish that task:

Identify the new items in List 2
Copy new items to Main List
Ignore old items in List 2

In this example, the two lists are in the same Excel workbook, on separate worksheets.

Both lists have the same 3 fields in columns A, B and C:

Email, LastName, FirstName

two Excel lists in workbook

Create Excel Table

The second list, List 2, is typed on the worksheet, but it isn't set up as a named Excel table, like the Main List.

It will be easier to work with List 2, if it's changed to an Excel Table, so follow these steps to make that change:

Select any cell in the List 2 data
On the Excel Ribbon, go to the Home tab
Click the Format as Table command
Click on one of the Table Styles, to select it
In the Create Table dialog box, check that the correct range is entered for the data - cells A3:C43, in the sample file.
Make sure there is a check mark for the setting, My table has headers.
Click the OK button, to create a named table for List 2.

change List 2 to named Excel table

Name the List 2 Table

Excel gives each new table a default numbered name, such as Table1.

To give the List 2 table a meaningful name, follow these steps:

Select any cell in the List 2 table
On the Excel Ribbon, at the far right, click the Table Design tab
At the left, in the Table Name box, type a new name, such as NewTable
Click anywhere on the worksheet, to exit the Table Name box

Note: The Main table has been named as Attendees.

change name for Excel table

Check for New Records

Some of the records in the new list (NewTable) are already in the Main List.

To check for new records, we'll add a column in the new list, and then enter a formula in that new column, to count any matching data.

Add New Column

To add the new column, follow these steps:

On the ListNew sheet, click in cell D3, beside the FirstName heading.
Type a heading name - InList
Press the Enter key, to move down to the next cell
The table automatically expands, to include the new column

add new column in Excel table

COUNTIF Formula

To check for new items, we'll use the Excel COUNTIF function, to see if each email address is already in the Main List.

The COUNTIF function counts items in a specified range of cells, based on the criteria that you enter in the formula. Its syntax is: =COUNTIF(range, criteria).

We need to check the Email column in the Main List (range), and see if the Email from List 2 (criteria) is found.

Enter COUNTIF Formula

To enter the formula in the new column, follow these steps:

Select cell D4
To start the formula, type this: =COUNTIF(
Next, go to the Main List, and click at the top of the Email column.
- That will add the table name and column name to the formula:
  - Attendees[Email]
Type a comma, and then go back to the NewTable
Click on cell A4, where the email for the current row is entered
- That automatically adds the column name to the formula
  - [@Email]
Type a closing bracket
Press the Enter key, to enter the completed formula in the cell

The completed formula is: =COUNTIF(Attendees[Email],[@Email])

Note: Because the formula is in a named table, the formula will automatically fill down, to the last row of data. If the formula does NOT fill down, point to the Fill Handle at the bottom right corner of cell D4, and double-click.

COUNTIF Formula Results

In the InList column, the results in each row show a 1 or a zero.

1: If email address is already in Main List, COUNTIF function found it, and returns a count of 1
0: If email address is NOT in Main List, COUNTIF function cannot find it, and returns a count of zero

Any row with a zero in the InList column is a new record, and we need to copy those to the Main List.

COUNTIF formula results 1 or zero

Sort Formula Results

To make it easy to copy the records, we can sort the list, so all of the new records are at the top of the Excel table.

Follow these steps to sort the results of the COUNTIF formula, in the InList column:

In the InList heading cell, click the drop-down list arrow
In the drop-down menu, click the first option - Sort Smallest ot Largest

All of the new records, with zero in the InList column, are sorted to the top of the Excel table.

change name for Excel table

Copy New Records to Main List

The final step is to copy the new records into the Main List.

To do that, follow these steps:

In the NewTable data, select the email, last name and first name, for all of the records that have zero in the InList column
Do not select the InList formula cells in column D
To copy the selected records, use the keyboard shortcut, Ctrl + C
Go to the Main List sheet, and go to the end of the Excel table
Select the cell in column A, in the first blank row below the table
To paste the copied records, use the keyboard shortcut, Ctrl + V

All of the new records are added at the end of the existing records, starting in the first column. The Excel table automatically expands to include the new rows.

new records pasted at end of main list

Compare Lists - FILTER Function

In Excel 365, you can use the new FILTER function to compare lists.

You can use it to filter out the items from the second list that are not in the first list. Then, copy the formula results, and paste at the end of the first list, as values.

This example has two lists with email addresses and names, just like the COUNTIFS example, in the previous sections.

Instead of adding a new column with COUNTIFS formulas, this example only needs one cell with a FILTER formula..

FILTER Function Formula

The FILTER formula will find names from the new list, which is in a table named MoreNames. In the formula:

COUNTIF function checks the main table, named Attendees, to see if the email is found there.
If email is NOT found (count = 0), FILTER function includes that email in New Names list.

To create the list of new names, follow these steps:

On the ListNew sheet, enter the following formula in cell E4
- =FILTER(MoreNames, COUNTIF(Attendees[Email], MoreNames[Email])=0, "No New Names")
Press the Enter key, to see the list of new names, spread over 3 columns

FILTER function extracts list of new emails

Add New Names to Main List

Next, follow these steps, to add the new names to the main list, on the ListOld sheet:

Select cell E4, where the FILTER formula was entered
Press the keyboard shortcut, Ctrl + A, to select the entire list of new names
Next, press the keyboard shortcut, Ctrl + C, to copy the selected cells
Go to the ListOld sheet
In column A, right-click in the first blank row below the old list -- cell A37 in this example
In the pop-up menu, in the Paste Options section, click Values

The new names are added, and the Attendees table expands automatically, to include rows with the new names.

Check the FILTER Formula

After adding the names, go back to the ListNew sheet. Now the formula cell shows the result, "No New Names", because all of the new list emails have been added to the main list.

The 3rd argument in the FILTER function syntax is [if_empty].
Use this argument to show a helpful text message, instead of an error value, such as #CALC!

In this example, the if_empty argument was set as "No New Names"

=FILTER(MoreNames, COUNTIF(Attendees[Email], MoreNames[Email])=0, "No New Names")