Looking up information in another spreadsheet—the trick to VLOOKUP
As a marketer, you have to keep track of a LOT of data (whether you know it or not). Just think about the numbers of sales, leads, campaigns, headlines, emails, Adwords, and mailers you’ve had to keep on top of in the last month, let alone having everything at your fingertips ready to present at your monthly, quarterly or annual meetings. There’s probably enough data points floating in spreadsheets on your computer that could likely make a sane person go a little crazy!
And if you’re like me, you’re keeping your data in different spreadsheets.
Maybe you have a sales spreadsheet, specifically for sales—data about the types of marketing that lead to customer engagement, the products or services sold and the contact information of the decision maker that might buy more from your business.
Let’s look at a Hypothetical company: FishCandy Vending Machines—their products are fish oil infused snack foods that help ensure joint health. FishCandy currently is pursuing new opportunities in businesses like yours (and they’re tracking their leads in a spreadsheet with data about the new lead: lead name, contact, product(s) the lead is interested in purchasing and dates of contact.
Fig 1. FishCandy Leads Data
But then you might have another spreadsheet that your assistant, Jan, put together with detailed contact information for each of the leads on your spreadsheet. The problem is that the order is different from your original leads sheet and Jan had identified additional businesses in her search that she thought might want to buy FishCandy products.
Fig 2. Prospective Lead Contact Information
There is no easy way to simply copy and paste one table next to the other!
Now your boss asks you to compile a mailing list for each customer by product(s) they are interested in purchasing. She wants to send out promotions specifically targeted to each of your product types and needs the info ASAP to get deals out to all of FishCandy’s leads.
It’s hard enough tracking data, but curating a data set that has EVERYTHING you might possibly ever want in one table is nearly impossible these days—especially with growing number of data sources (just think of the thousands, if not hundreds of thousands of data points your website generates each month!).
Don’t worry—you won’t have to look up and combine your data by hand. Here is a simple way to combine data using Excel that will save you hours of pounding your head on the table.
It’s called VLOOKUP and if you haven’t used this Excel function to organize your data, I guarantee it a time saver!
Lets’ get started. For today, I’m focusing on using the tool with two simple tables—but the function works with really large tables just as well. And you can look up data from multiple tables to get the right fields in the spreadsheet you are putting together.
Just to recap—we have 2 tables: one full of customer contact information that Jan has put together that has more leads than you’re currently interested in pursuing, and you have put together a leads table.
Our problem: you need to add the contact info to your leads sheet quickly with VLOOKUP to give a list of leads, the products they are interested in and their contact info so that she can get product promotions sent out to those interested customers.
Now for the fun part: simplifying life with VLOOKUP!
Here are the simple steps:
- In the table you wish to add additional information (here, I am going to add an Address Column to the Leads table)- insert a column and name it the data field you wish to append. I am going to simply name this column address.
Fig 3. FishCandy Lead table with added ‘Address’ column
Before you start combining information, you need a column that is shared between the two tables—here I will combine information from the two tables based on the “Lead” column.
- Type in the following formula:
=VLOOKUP(A2, A:C, 4, FALSE)
Fig 4. Typing VLOOKUP formula into new Address column. Start by selecting the lookup_value which is the lead associated with the current row (2nd row). Add a comma after the cell you selected.
Fig 5. Selecting the information from the contact information table. Select the pieces of the table you need for the desired information from the contact information table. Be sure to start with the lead column, since this is the column Excel is using to reference from your Lead table. Here, I selected columns A through C, since the address column is located in column C.
Type another comma then type the number of columns you need to move over to get to the address column. Since Address is located in the 3rd column from the selected columns (Leads column through Address column), enter the number 3:
Follow the 3 by typing another comma.
Last, type the word FALSE (I explain why false below).
And then you see the Address of the Globex lead in the Address field:
Fig 6. Globex address added to the Leads table
Now simply go over to the bottom right corner of the cell located at E2 and double click to fill the field down the rest of your dataset. You should see your mouse arrow turns to a ‘t’:
And you’re left with the data your boss has been looking for—and the best part: it took you SECONDS to produce!
Just in case you need a little more info on what to put in the VLOOKUP formula, here are explanations of the 4 pieces of data Excel requires:
=VLOOKUP(Look_up value, Table Array, Column Index number, Approximate or Exact Match)
- Look_up value—Excel asks you to input a look up value. To look up information from one table to another, you need a common field that has only one entry in the table you are taking the information from. If you look up a field that has multiple entries for a specific term, Excel will take the first entry (that’s likely not going to be very useful). Here each company name is only represented once in the contact table. The lookup value is the entry from the table that you are currently working on. Here the company name falls in column B. Since I am currently working on row 2, I selected B2 as the lookup value for this entry.
- Table Array—select the columns in the table you are looking up. Note: the first column needs to be the column with the lookup term—that means this column has to be placed to the left of the column you are interested in adding. Here I selected A:C (I selected all data in columns A,B, and C). My lookup value is in column A of the contact table and the data I am interested in adding is in column C. For simplicity I recommend selecting the entire column and making sure you have one table per spreadsheet- this makes working in excel a lot easier.
- Column Index number—type in the number of the column you are interested in. Since I selected 3 columns and the location data was in the third column, I entered a ‘3’ for the column index.
- Approximate or Exact Match—I always type FALSE, which gives me only entries that match exactly between the two datasets. If you expect there are slightly different variants between your tables and know that those variants are the same, you can have excel match similar items, but I highly recommend you keep your naming system consistent and use exact match only here—it will save you a lot of time troubleshooting if there really was a problem!
Now that you’re a fancy shmancy expert in VLOOKUP—try it out for some of your own work!
And Remember that we’re here to help with all of your business computer headaches and have experts that know the ins and outs of business technology. If you have questions about how to leverage technology for your marketing or any part of your business, please contact me today.
