PPC VLOOKUP: How to Use VLOOKUP for Paid-Per-Click Marketing

VLOOKUP is one of a number of handy Excel functions for PPC. For grizzled PPC campaign managers this function is likely very familiar, but as a former philosophy major who fulfilled his math requirement with a class in logic (taught by a philosophy professor) I think it’s helpful for non-Excel ninjas who are new to PPC to understand:

1. How the Excel VLOOKUP function works
2. How it can be applied to pay-per-click management

As a result in this article we’ll walk through some specific applications and resources offering more information on using the VLOOKUP function for PPC.

How to Do a VLOOKUP

Before we dig into the applications of the VLOOKUP function for PPC I want to go over how to actually use the VLOOKUP function. Here are a couple of videos that should help you to learn the function:

And another example of how to use the function:

Now you may not actually have a reference table and of course we don’t have the color codes or state fees outlined above within our paid search campaigns, but either of those videos should give you a strong foundation for the syntax in the VLOOKUP function and how to format it properly. From there, we can apply the function within various paid search efforts.

How Might We Use the VLOOKUP Formula in PPC?

The best use of the VLOOKUP function for PPC is in comparing data from multiple date ranges. There have been some great posts outlining creative applications of VLOOKUP both right here on the WordStream blog and by our friend Chad Summerhill:

• Comparing PPC Month Over Month – Amy Hoffman from the team at Hanapin wrote up a great, in-depth post on how to compare PPC data month-over-month. This is a fantastic means of figuring out “what’s changed” in your accounts to help you identify opportunities or troubleshoot issues within a campaign. As Amy outlines in her post, you can leverage this methodology to help you compare all kinds of metrics – from Click-Through Rates to – to diagnose dips or surges.
• Calculating Peer Comparisons in PPC Data – Here Chad outlines a similar methodology (and even offers up a handy spreadsheet) for comparing data using the VLOOKUP formula, though in this case he’s walking you through how to analyze your data against it’s peers to help surface areas of your account that are the most in need of your attention. This sort of context and workflow prioritization is critical to successful PPC campaign management amidst the sea of data available in paid search accounts.
• Optimizing Your Paid Search Account Structure – Here Josh Dreller outlines another interesting application for the VLOOKUP function in developing strong account structure.

The ability to do in-depth data comparisons within your paid search campaigns is crucial, and the VLOOKUP formula helps make this possible within Excel, so it’s an important function to be familiar with.

Want More Excel Goodness?

If you’re still jonesing for some more Excel data-munging, we made a great addition to the WordStream Quality Score Toolkit not too long ago that offers a ton of actionable insight into your accounts’ Quality Scores. Get it here if you missed it the first time around. Looking to create impressive charts to showcase PPC with Excel? Try out this tutorial on making bubble charts on Excel.