Append data from one list to another

Introduction

Often you will have one list of say, donors and their email addresses, and a second list of some donor summary information like most recent contribution (MRC) date and amount. You need to send these folks an email that contains personalized content based on their MRC Date and Amount, but you don’t know how to get the MRC data from one list appended to your list of email addresses.

Our approach will use the vlookup() function to accomplish this task. vlookup() is a powerful function that “looks up” values from one list based on a common key present in another list.

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 email address as our common key to add MRC Date and MRC Amount to a list that already contains Email Address and Name:

2. Structure your data

In order for vlookup() to work, the common key for that data you’re looking up needs to be to the left of the data you’re trying to return. In our example, this means that email address needs to be in a column to the left of our MRC Date and MRC Amount, like this:

The reason for this will become clear shortly, but by way of example, here is a structure that would not work with our approach:

This won’t work because the common key is to the right of the columns we’re trying to append to our other list. Note that there are other approaches in Excel to perform the lookup with data structured this way, but in my experience, it’s easier just to move the common key column to the left and use vlookup().

3. Perform the lookup

vlookup() may seem like an intimidating beast at first but don’t be deterred by the seeming complexity of this explanation. With just a little bit of hands-on practice, you’ll be a vlookup() wizard in no time. vlookup() is a function that takes in four arguments, parameters passed into the function to tell it what to do:

• lookup_value: The value for the common key in your main list that you want to “look up” in your secondary list. In our example, we will use a cell reference to the email address in column A for this argument.
• table_array: The range of cells that contains your second list, where you are “looking up” the value in lookup_value. In our example, we will use the range F:H, the column range that corresponds to our List B. The first column of this range should hold the common key, email address in our example.
• col_index_num: The column number within table_array that contains the value we want to return. In our example, since our table_array is F:H, a value of 1 here would correspond to the F column (and would return Email Address values), 2 would correspond to the G column (returning MRC Date values), and 3 would correspond to the H column (returning MRC Amount values).
• range_lookup: A true/false value that tells Excel whether to perform a range lookup or look for an exact match. For this use case, we will always use the falue FALSE for this argument to force Excel to use exact matches on our common key.

Take a deep breath, we’re almost there. We can use all these building blocks to put together our first formula, looking up the MRC Date for Marius which we will enter in cell C3:

=vlookup(A3,F:H,2,FALSE)

See? Not so inimidating. Let’s break it down. This formula is telling Excel, “Take the value in cell A3 and look for it in the first column of the range F:H. If you find a match, return the value from the 2nd column in the range (MRC Date, column G). Oh, and make sure it’s an exact match, none of that range lookup business!”

4. Fill the formula

Now that we have Marius’s MRC date sorted out, we just need to fill down the formula to the rest of the rows:

Now we’ve got everyone’s MRC date from List B into List A. Take note of a few things:

• This works even though the order of the email addresses in List A and List B are different. Excel is looking up email address matches, your data does not need to be sorted in any particular way.
• In our contrived example, each of the two lists contains the exact same people. In reality, this won’t be the case and that’s ok! This approach will work even if List B contains more poeple than List A does.
• In our example, everyone in List A is also present in List B. In the event that a person in List A was not in List B, our formula would return the value #N/A, meaning “no value available.” If this happens, double-check that you’ve gotten the formula right and then do some manual verification that you’ve got the right data in both lists.

5. Repeat the process for MRC Amount

Now we just need to do the same thing for MRC amount. The approach is the same and the formula is nearly identical. The only difference is that instead of returning the value from column 2, we want to return the value from column 3. This makes our formula:

=vlookup(A3,F:H,3,FALSE)