- The Search Agents - http://www.thesearchagents.com -

How-To: Manipulating Data in Excel

Posted By Silvia Anghel On May 16, 2011 @ 5:56 am In Featured,SEM | 4 Comments

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’?

[1]

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.

[2]

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.

[3]

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) )

[4]

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?


Article printed from The Search Agents: http://www.thesearchagents.com

URL to article: http://www.thesearchagents.com/2011/05/how-to-manipulating-data-in-excel/

URLs in this post:

[1] Image: http://www.thesearchagents.com/2011/05/how-to-manipulating-data-in-excel/table-1-2/

[2] Image: http://www.thesearchagents.com/2011/05/how-to-manipulating-data-in-excel/table-2/

[3] Image: http://www.thesearchagents.com/2011/05/how-to-manipulating-data-in-excel/table-3/

[4] Image: http://www.thesearchagents.com/2011/05/how-to-manipulating-data-in-excel/table-4/

[5] Modeling Clickthrough Probabilities: http://www.thesearchagents.com/2009/08/modeling-clickthrough-probabilities/

[6] Analyzing Campaign Traffic by Average Position: http://www.thesearchagents.com/2010/06/analyzing-campaign-traffic-by-average-position/

[7] Google, Can You Hear Me Now?: http://www.thesearchagents.com/2009/07/google-can-you-hear-me-now/

Copyright © 2009 The Search Agents. All rights reserved.