Using Excel Formulas for PPC Optimization

Most of you probably know how to use the VLOOKUP formula for PPC, but chances are that you probably haven’t heard of the more powerful INDEX-MATCH lookup method.

Lookup formulas are an essential tool for every PPCer and are used primarily to add context and comparison metrics to your PPC analysis.  Here are a few of my favorite uses of lookup formulas:

Using lookup formulas to add context to PPC data analysis can really speed the discovery of insights.  The VLOOKUP is great for comparisons, but it does have its weaknesses.

One weakness is that, by default, the VLOOKUP returns an approximate match and not an exact match.  This can cause problems for inexperienced VLOOKUP users by returning false values.  If you are using the approximate match, then the sort order becomes very important.

Another weakness is that the VLOOKUP can only lookup values in the first column of a table array, and can only return values from columns to the right of the first columns—this can be annoying.

Of course, most of us have been using the VLOOKUP successfully for years now, and if it was the only option available we would still considered ourselves blessed.  However, there happens to be another lookup up method that doesn’t have these weakness and promises to be more powerful and flexible—INDEX-MATCH.

Using the INDEX-MATCH lookup method

I’ve just started to transition to the INDEX-MATCH formula, so I am by no means an expert, but you can use it for all of the analysis types listed above without having to worry about the limitations of the VLOOKUP.  The formula is actually made up of two Excel functions:

=INDEX(reference, row_num, column_num)

  • reference—a range of cells
  • row_num—the row in reference from which to return data.
  • column_num—the column in reference from which to return data.

If reference is one row or column, INDEX can use this syntax: =INDEX(reference, cell_num)

=MATCH(lookup_value, lookup_array, match_type)

  • lookup_value—the value to match in lookup_array.
  • lookup_array—range of cells with data.
  • match_type—specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.

Here’s an example of using the INDEX-MATCH formula that returns the sum of impressions for a previous time period:

=INDEX(reference, MATCH(lookup_value,lookup_array,match_type))

As you can see, the INDEX-MATCH formula is returning a value that is located to the left of the lookup array.  This is convenient because a primary key was created for the lookup value to the right of the both sets of data.  This keeps us from having to insert a column into the original report data just to get it on the right side of the reference column.

The INDEX-MATCH formula is quickly becoming part of my everyday PPC analysis.  I hope you also find in useful.

##

This is a guest post by Chad Summerhill, Author of the blog PPC Prospector, provider of free PPC tools & PPC tutorials, and AdWords Specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).

Posted by admin in Pay Per Click on April 29,2011

Tags:

Comments:

Thanks Chad for posting this. You can also use Excel to build great marketing forecasts based on demographic data from a CRM system, report on Campaign performance and tie in your budget/costs into your end game goal.

Post a comment

CommentLuv Enabled