How to match your email list to a voter file in Excel
As Head of Product for Involved, an online survey software and CMS for local government, I’ve talked to a lot of city officials and campaigns who struggle to identify and grow their contacts.
Today, I wanted to share a quick trick I used recently to help a city councilman from Hoboken segment his email list of over 11,000 contacts by ward.
If you are unsure how to acquire a voter list in your area, you can view the state-by-state laws here. Once you have acquired your voter list and exported your contacts, you should be all set to follow along with the steps below to match them:
Note: This does require having first and last name information for your contacts. If you do not have this information, you can use Involved to try matching on email alone.
- Place both your email list and your voter list in separate sheets within the same workbook
2. Add a new column in column A of your email list table
3. In the first cell of the new column, add first and last name together in all upper case using the following formula:
=CONCATENATE(UPPER(first name cell), “ ”,UPPER(last name cell))
4. Copy this formula to all rows in that column by double clicking in the bottom right hand corner of that cell
5. Copy the entire column and use paste special in the same column to replace the formula you entered with the name values you created
6. Repeat steps 2–5 with your voter registration list table
You should now have one column in each table that includes first and last name in all caps.
We will now use a function called VLOOKUP (stands for vertical lookup) to identify the matching rows in each column and grab the corresponding email addresses.
7. Create a new column in your voter registration table for email addresses
8. In the first cell of the new column, look up the corresponding email address of that contact using the following VLOOKUP function
=VLOOKUP(name cell, email list table array, the number of the column with email addresses)
#N/A is a common response to the VLOOKUP result in this case and just means that no matching email address was found.
9. Copy the VLOOKUP formula to all rows in that column by double clicking in the bottom right hand corner of that cell
Great, you should be all set!
You can now sort your table by email to bring all of the matched emails up to the top. Also, make sure to drop all matched emails back in to your contact management system of choice and add any necessary custom fields that you would like.
If you have any questions, please feel free to contact me at jacob@involved.vote.
Note: If you are only able to append address information but not ward/precinct, Involved can automatically determine a contact’s ward and precinct by physical address and visualize your contacts on a geographic heat map.
