This post is an excerpt from the video series 4 Essential Microsoft Excel Skills Every Marketer Should Learn. If you want to become a master of the almighty spreadsheet, watch the full video series here.
I know, I know … “VLOOKUP function” sounds like the geekiest, most complicated thing ever.
But trust me: as was the case with pivot tables, Microsoft Excel's VLOOKUP function is easier to use than you think. What’s more, it is incredibly powerful, and is definitely something you want to have in your arsenal of analytical weapons.
So, what does VLOOKUP do, exactly? Here’s the simple explanation: The VLOOKUP function searches for a specific value in your data, and once it identifies that value, it can find -- and display -- some other piece of information that’s associated with that value.
In practical terms, that means you can take data from one source and integrate it with data from another source in order to see the bigger picture.
Here’s an example:
Let’s say you’re looking through your HubSpot data and are checking out which of your site pages your contacts have viewed. You’re also paying attention to whether or not any of those contacts have converted into customers.
Then it hits you: In addition to knowing which of those contacts have closed, you want to know how much MRR (monthly recurring revenue) each of them brings in. That way, you can tie your revenue back to your site pages and do some analysis to see which pages are having the biggest impact on your bottom line.
There’s only one problem: Your MRR data lives in your CRM. And while you could manually look up each and every contact in your CRM to find their MRR, and then manually match those values to their corresponding contacts in your HubSpot data, the whole process would be ridiculously time-consuming and impractical.
That’s where the VLOOKUP function comes in.
VLOOKUP can automatically find the corresponding MRR for each of your contacts (which means you don’t have to manually search through your CRM data) and it can then automatically populate the correct value into the spreadsheet with your HubSpot data (which means you don’t have to manually enter values).
The secret to how all of this works? Unique identifiers.
A unique identifier is a piece of information that both of your data sources share, and -- as its name implies -- it is unique (i.e. the identifier is only associated with one record in your database). Examples of unique identifiers include product codes, stock keeping units (SKUs), and, in the case of our example, email addresses.
Since HubSpot and most CRMs both use email addresses to uniquely identify the contacts in their databases, you can use “email address” as your unique identifier to execute a VLOOKUP.
Alright, enough explanation: let's see the VLOOKUP in action!
How to Do a VLOOKUP
In the video below, we're taking the pivot table we made in video #2, pasting the values into a new sheet, and using it as an example report. We then use the VLOOKUP function to match blog post authors (from our second data source) to their corresponding post titles. In this instance, we're using post title as our unique identifier.
Author's note: Keep in mind there are many different versions of Excel, so what you see in the video above might not always match up exactly with what you'll see in your version. That's why we encourage you to download the written instructions and demo data so you can follow along.
Want to learn to do more in Excel? Download the full video series, 4 Essential Microsoft Excel Skills Every Marketer Should Learn.
What other Excel skills would you like to learn about? Let us know in the comments section below!