Who is in both lists?

Introduction

Often in fundraising you will have two lists of constituents and you want to know who is in both lists. Excel is here to help with this task! We will use the countif() function and a helper column to search one list for records that exist in another.

1. Identify a common key

In order to determine which donors are on both lists, you need a common key - a piece of information that’s in both lists that can be used to uniquely identify a constituent. Often this will be a Donor ID or email address. In the example document, we’re using Donor ID as our common key.

2. Create a helper column

To the right of your existing data, add a helper column which will be used to tell us whether a given donor is in the other list. In this example, we’re using a column named “In List B?” but you can name this column anything you’d like:

3. Use countif() to match across lists

countif() is designed to tell you how many times an instance of one value appears in some range of cells. The function takes two arguments, parameters passed into the function to tell it what to do:

• range: This is the range you want to search. In our example, we will be using the Donor IDs from List B as our range. These IDs are in column F.
• criteria: This is the value we are searching for in our range. In our example, we will be using the Donor ID from List A which are in column A.

We will also be using a trick to turn the countif() result into a simple true or false value. Let’s enter our formula into cell D3 and then we’ll break it down:

This formula is telling Excel, “Search the range F:F (the entire F column) for the value that’s in cell A3 and tell me how many times it appears. Then, check if that number is greater than 0 or not.” This will result in the value TRUE if the Donor ID in cell A3 exists in column F at least once, and FALSE otherwise.

4. Fill the formula down

Now all that’s left to do is fill down the formula to our remaining rows:

Every row that contains a match between the two lists will show TRUE while rows that do not contain a match will show FALSE.