How to Use VLOOKUP in Excel 2013 and 2016 [Video Tutorial Included]

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, this means you can take the revenue data from your second spreadsheet and integrate it with the customer data in your first spreadsheet in order to reveal the bigger picture about your business’s performance.

Below, you’ll see a five-step guide to performing this VLOOKUP example, followed by a video tutorial for using VLOOKUP to organize a list of blog posts.

How Does VLOOKUP Work?

The secret to how VLOOKUP 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). Unique identifiers include product codes, stock keeping units (SKUs), and customer contacts.

Since HubSpot and most CRMs both use email addresses to uniquely identify the contacts in their databases, HubSpot customers can use “email address” as their unique identifier to execute a VLOOKUP.

Steps to Using VLOOKUP in Excel

Take the VLOOKUP example above. 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. For your reference, here’s what a VLOOKUP function looks like:

VLOOKUP(lookup_value , table_array , col_index_num , range_lookup)

In the steps below, we’ll assign the right value to each of these components, using customer names as our unique identifier to find the MRR of each customer.

Step 1: Identify a Column of Cells You’d Like to Fill With New Data

Entering a new column title in Excel in order to use the VLOOKUP function

If this data is coming from a pivot table made in Excel, copy the data into a new spreadsheet so the VLOOKUP function can freely read this data.

Then, label a column next to the cells you want more information on with a proper title in the top cell, such as “MRR,” for monthly recurring revenue. This new column is where the data you’re fetching will go.

Step 2: Select ‘Function’ (Fx) > VLOOKUP and Enter Your Starting Cell

Function (Fx) icon in Excel, which accesses the VLOOKUP formula builder

To the left of the text bar above your spreadsheet, you’ll see a small function icon that looks like a script “Fx.” Click on the first empty cell beneath your column title and then click this function icon.

Select “VLOOKUP” from the list of options that appears, and then re-click the cell you’ve highlighted and enter the cell you’re trying to find a match for. In this case, it’s A2. You’ll start migrating your new data into E2, since this cell represents the MRR of the customer name listed in A2.

Step 3: Enter the Table Array and Column Number You’re Searching Through

      

Then, next to the “table array” field, enter the range of cells you’d like to search and the sheet where these cells are located. The VLOOKUP form will help you fetch the correct page.

Beneath this field, you’ll also enter the “column index number” of the table array you’re searching through. For example, if you’re focusing on columns B through K (notated “B:K” when entered in the “table array” field), but the specific values you want are in column K, you’ll enter “10” in the “column index number” field, since column K is the 10th column from the left.

Step 4: Enter Your Range Lookup

In contexts like monthly revenue, you want to find exact matches from the table you’re searching through. To do this, enter ‘FALSE’ in the “range lookup” field. This tells Excel you want to find only the exact revenue associated with each sales contact.

Step 5: Click ‘Done’ (or ‘Enter’) and Fill Your New Column

In order to officially bring in the values you want into your new column from Step 1, click “Done” (or “Enter,” depending on your version of Excel) after filling the “range lookup” field. This will populate your first cell. You might take this opportunity to look in the other spreadsheet to make sure this was the correct value. 

Entering the first new value from Excel's VLOOKUP formula builder

If so, populate the rest of the new column with each subsequent value by clicking the first filled cell, then clicking the tiny square that appears on the bottom-right corner of this cell. Done! All your values should appear.

Filling a new column with data from Excel's VLOOKUP formula builder

Alright, enough explanation: let’s see another example of the VLOOKUP in action!

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.

Download demo data | Download instructions (Mac) | Download instructions (PC)

Want to learn to do more in Excel? Download the full video series, 4 Essential Microsoft Excel Skills Every Marketer Should Learn.

excel spreadsheet microsoft skills

About the Author

Leave a Reply

  • Archives

  • More than 200,000 likes in social media

    Why us

    Projects

    5864

    Clients

    1262

    Positive Press

    11

    Impressions

    102448010

    Days

    365

    Updated on 22 October 2018

    Please wait...

    Subscribe to our newsletter

    Get the latest news and updates from Godspeed Network. Enter your email address and name below to be the first to know.