Why Learn Vlookups?

So, you’re a PPC veteran.  You’ve dabbled in quick Excel shortcuts that get the job done faster.  You know that =proper() will capitalize your ad copy, and that =LEN() will count characters.

But you have so many things on your plate that are way more important than learning new Excel functions.  Things due.  Now.

So why addlearning vlookups to your ever-growing list?  The truth is, vlookupsare actually game changers for PPC account managers. Learning them means you’ll spend less time bogged down with menial data entry tasks and more time on more strategic tasks – like keeping up with an ever-changing industry.

In Layman’s Terms

Let’s forget PPC for a moment.  Instead let’s imagine you’re managing a fundraiser for a local charity.  You have two lists: onewith6,000names and home addresses, and a separate list with the same names andtheir associated email addresses. How do you get the person’s name, home address, and email all in the same excel sheet?  The answer is vlookups.

Vlookups work like automated address books. You essentially“tell” them to associate the person’s name with the email address.  With vlookups, you can quickly and efficiently tell Excel what two unique things to associate together, and Excel will marry them on any sheet instantly.

PPC Applications

Okay, now putting that PPC hat back on, here’s what you, the time-conscious PPC-er can do with vlookups:

  • Reassign landing pages for a large number of keywords or ad copy
  • Compare data from different date ranges for numerous keywords
  • Combine data from two different reporting sources on one sheet
  • Check whether new keywords already exist in an account
  • Identify and troubleshoot discrepancies between data sources

Example:  Combining Data from Multiple Sources

Let’s say in order to analyze performance lifts around the holidays, you need to pull conversions for every day of the year, and there are two reporting sources. One conversion type is tracked through Adwords (tracking form fill outs) and the other is tracked through an external system (tracking phone calls).  How can both form fill outs and calls be pulled into the same sheet, by day?

  • Pull a report from Adwords, including a column listing the day of the year and total conversions for that day.
  • Label this report tab “Form Fill Outs.”
  • Pull a report from your call tracking system with one column for the day of the year, and another with the total calls for that day.
  • Move your call tracking report to the second tab of your “Form Fill Outs” report, giving it a sensible name like “Calls.” Later, you’re going to tell excel to associate a day of the year with the number of calls for that day.  But in order for excel to understand, the characters in 1/1/17 must match EXACTLY on both tabs; it won’t know that “1/1/17” is the same date as “January 1st” So first, make sure that the formatting of the dates matches exactly on both tabs.

Tab 1: “Form Fill Outs”

Tab 2: Calls

  • Click to your first tab that contains Adwords conversions.
  • Insert a new column directly to the right of the “Date” column, and label it “Calls.” You’re going to bring calls into your sheet containing  form fill-outs.  Now for the excel formula.
  • Enter into the cell below “Calls”:
    1. =vlookup(

  • Tell excel which item to reference. Like the address book example, you’ll want to tell excel to reference the person’s name and match it with another value. But in this case, you want excel to reference the date:
    1. Example: =vlookup(A2,

  • Tell excel what two items to match together (you want it to match the date with calls). Click to your second tab and click drag over all of the dates and call values, ending with a comma:
    1. Example: =vlookup(B2,Calls!A2:B12,

  • Tell excel to only to reference the exact date by adding“, 2, False)”
    1. Example: =vlookup(B2,A2:B75,2,FALSE)

 

  • Click “enter.” You should see the matching number of calls for that date in your excel sheet. Before you drag the formula down, make sure that every time you use the formula, it references the same cell range. Static the row using “$” (see below screenshot).
    1. Example: =vlookup(B2,A$2:B$75,2,FALSE)

Now on your first tab, drag the formula down for every cell in column B.

Now you should see, on your first tab, all of the corresponding calls and form fill outs in one place.  Well done, you’re a mater at vlookups!  With a little practice, you can apply this same process to save tons of time on your PPC management!