Name Match: Who's In and Who's Out

Andrew Kamphey -

Hey there, and welcome to another helpful tutorial from Better Sheets! Today, we're tackling a common spreadsheet challenge: comparing two lists to find out what's missing from one. This tutorial was inspired by a question from our member, Wallace.

Let's say you have a master list with over 700 names and a new list from a recent meeting containing 175 names. How will you identify which names from the new list aren't on the master list so they can be added.

This task can seem daunting, especially if you’re used to hunting and pecking through your lists manually. But don't worry—I'm going to show you how to do it efficiently with just a few simple formulas. No scripts, no complex coding, just basic Google Sheets formulas that will make your life easier. Let's dive in!

1. Setting Up Your Lists

Before we get into the formulas, let's organize our lists. You’ll need two separate columns or sheets—one for your master list and one for your new list.

Example Setup:

  • Master List (MAIN, Column A): Contains 700+ names.
  • New List (NEW, Column A): Contains 175 names from the recent meeting.

2. Using the MATCH Formula

The first formula we’ll use is the MATCH formula. The MATCH formula is a powerful tool that helps you find the position of a specific value within a range of cells. In this case, we'll use it to determine if a name from the new list exists in the master list.

  • In your new list (Sheet 2, Column B), type the following formula next to the first name: =MATCH(A1,Sheet1!A:A,O)
  • A1 is the name from your new list that you’re checking.
  • Sheet1!A is the range of the master list.
  • The 0 specifies that we want an exact match.

Let's verify this on the main sheet. If our formula is correct, we should find 'Safiyah Southern' on row 191.

  • If the name is found in the master list, the formula will return the row number where it was found. If the name isn't found, you'll see an #N/A error.

3. Handling Errors and Filtering

Let’s start by sorting columns A and B. This will make it easier to identify the names that are missing from the master list.

Now, seeing a bunch of #N/A errors can be a bit messy. Let’s clean this up using the IFERROR function, which allows us to replace errors with 0.

=IFERROR(MATCH(A1,MAIN!A:A,0),)

4. Automating the Process with an Array Formula

If you have a long list, manually dragging the formula down to each cell can be time-consuming and tedious. Thankfully, we have a powerful tool called ARRAYFORMULA that can automate this process.

=ARRAYFORMULA(IFERROR(match,A1:A,MAIN!A:A,0),0))

As you can see, we only needed to enter one formula using ARRAYFORMULA and the names without a match (or with a 0 result) are now listed below. Now, what we need to do is instead of having 0, let's put the word "Add".

=ARRAYFORMULA(IF(ISBLANK(A1:A!),,IFERROR(match(A1:A,MAIN!A:A,0),"Add")))

and all the names with 0 will now have the word "Add".

5. Filtering Out Names to Add

Since we’ve identified which names need to be added, let’s filter them into a separate list.

  • In a new sheet which we named "ADD", use the FILTER function to pull only the names marked with "Add":

=FILTER(NEW!A:A,NEW!B:B="Add")

This will provide a clear list of all the names that aren’t on the master list. You can easily identify these names by looking for the word ‘Add’ in the new column. You can just copy and paste them into the master list, ensuring that your data is complete and accurate.

With your filtered list in hand, you can now easily copy and paste these names into your master list. If your master list is on a separate sheet, just be sure to update the references in your formulas accordingly.

A simple, effective way to compare two lists in Google Sheets, identify missing names, and keep your records up to date.

If you found this tutorial helpful and want access to more tips, tricks, and exclusive content, consider joining Better Sheets. Our membership offers access to over 100 videos, scripts, and templates to help you master Google Sheets. You can even start for free and explore over 60 videos to get started.