How-To: Manipulating Data in Excel

Posted on Monday, May 16th, 2011 by Print This Post Print This Post

Categories - Featured, SEM

Anyone who has ever managed a PPC campaign of any size has had to manage huge data sets within Excel. VLookups has become a common method of manipulating and analyzing large data tables.  But there are two other functions, Index and Match, that provide additional functionality which many help you better optimize your PPC campaigns. Let’s take a look at the benefits of these two functions, and explain how you can go about integrating them into your data management strategy.

Why use it?

Using INDEX with MATCH instead of VLOOKUP to look up values in a table is preferable for the following reasons:

  1. You can look up values in any column in the table (VLOOKUP limits you to the left-most column).
  2. You can return values from a column referenced by name, rather than by relative position (like “5”, as VLOOKUP requires).  For example, you can return from the “Cost” column (by looking up the word “Cost” in the row of column headers), instead of having to count the number of columns from the left until you get to the “Cost” column, and then give that number to VLOOKUP.

 

How does it work?

To illustrate, let’s try to answer this question: What is the Cost for keyword ‘mazda’?

The INDEX function returns a value from the lookup table, at the intersection of a certain row and column.  It takes these parameters as inputs:

INDEX (reference, row_num, column_num)

reference:  the entire table from which to look up

row_num:  the row in the table from which to return values; this will get calculated using a MATCH function

column_num:  the column in the table from which to return values; this will get calculated using a MATCH function

 

Using the MATCH function for the row_num and column_num parameters:

The MATCH function looks up a value in a one-dimensional  array (i.e. a single column or row), and returns the index position where the match was found:

MATCH(lookup_value, lookup_array, match_type)

lookup_value:  the value you’re looking for

lookup_array:  the column or row in which to look for the value

match_type:  use “0” for an exact match

row_num parameter

The row_num parameter represents the row from which you want your value returned by the INDEX function.  In our example, you need to look for the keyword “mazda” and locate the row of the table in which it is found.  This will be used as the second parameter of the INDEX function.  To obtain this value, use a MATCH function, for which the lookup_array is the Keyword column in your table.  Make sure to give the lookup range starting from the column header to the last row, inclusive.  When a match to your looked-up value (“mazda”) is found, the function will return the row index of the matched value.

Example: We’re looking for “mazda” (cell C20), in range C9:C15.  The third parameter of 0 indicates that the lookup should return an exact match:

MATCH (C20,C9:C15,0)

This will return “5”, which is the position of the row in which “mazda” was found.

column_num parameter

The column_num parameter represents the column from which to return the value.  Make sure to give the lookup range starting from the left-most column to the right-most column, inclusive.  The look up value will be the column name, like “Cost”.  When a match to the looked-up value is found, the function will return the column position of the matched value.

Example: We’re looking for “Cost” (cell B21), in range B9:M9:

=MATCH (B21,B9:M9,0)

This will return “10”, which is the column position in which “Cost” was found.

To now obtain the value that we are looking for (the Cost for keyword “mazda”) we construct an INDEX function by combining the two MATCH functions described above, using the whole table as the lookup array:

INDEX ( B9:M15, MATCH(C20,C9:C15,0), MATCH(B21,B9:M9,0) )

The value 801.91 is returned.  Indeed, this value represents the cost of the “mazda” keyword.

What’s your current approach to  managing data sets in Excel? Could Index and Match help streamline your data management efforts?

Tags | , , , ,

3 Responses to “How-To: Manipulating Data in Excel”

  1. TrafficColeman says:

    People have been doing this for years now, by even changing clickbank stats. This is the power of excel.

    “Black Seo Guy “Signing Off”

Trackbacks/Pingbacks

  1. PPC Weekly Roundup — the Get In Shape Edition
  2. How To Pay Per Click | Adsense Information Blog - Resources Tips and Tricks

Leave a Reply

Follow Us on Twitter

Authors