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.
The syntax for the VLOOKUP function is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- 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
You can download the Excel file here.
Couple More For The Road
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.
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.
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!