The VLOOKUP function in Excel is one of the most essential functions marketers need to master. It’s a staple in my tool set because it allows you to join any number of data sets, as long as they have one column in common. Sadly, as is the case with most really useful Excel techniques, it can be quite intimidating for newbies. I hope to remove some of that intimidation with this video.
Syntax
The syntax for the VLOOKUP function is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Video Overview
In this tutorial I take a custom landing page report from Google Analytics (which you can access here) and marry it to a data set I got from a Screaming Frog crawl. I cover these techniques:
- Concatenating hostname and landing pages to match the format of URLs in Screaming Frog exports
- Alternating between absolute and relative references for formulas
- Building flat table custom reports in Google Analytics (learn more with this post)
- Working with named ranges in Excel
- Concatenating on the fly inside a VLOOKUP
- Making VLOOKUPs dynamic
- Error handling for VLOOKUPs
Video
Excel Download
You can download the Excel file here.
Couple More For The Road
Partial Matches
One more cool technique you can jam with VLOOKUPs (but didn’t work with my data set) is to use wildcard characters to look for partial matches in text. (Learn more about wildcard characters from the Microsoft site.) This transforms the matching functionality to a makeshift CONTAINS function. (I’ve requested that the Excel teams at Microsoft add a CONTAINS function.
Trims
Some marketing data exports can be pretty messy, but if they contain things like extra spaces before or after your lookup text or table array, your VLOOKUPs will break. If this happens to you, you can wrap either your lookup_value or table_array in a TRIM function to remove all extraneous spaces.
Your Turn
Do you have some wild and crazy things like you do with VLOOKUPs? If so, please share with the rest of the class in the comments!
For the longest time, I knew VLOOKUP was something I needed to understand but the compelling use case never really came along until your blog post. Now I don’t understand how I’ve lived without it. I had that same reaction with pivot tables. It didn’t hurt that your example had a tab for a Screaming Frog crawl alongside Google Analytics usage data. That’s when the light bulbs started going on. THANK YOU THANK YOU!
This made my day. No … week. This is why I do what I do … 🙂
🙂 Rock on Annie!