" />

## How-To: Manipulating Data in Excel

Posted on Monday, May 16th, 2011 by

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

## Related Articles:

Silvia Anghel is Sr. Analytics Developer for The Search Agency. In addition to being an Excel nerd, she spends her working days writing VBA code for analytical tools and Excel add-ins.

Tags | , , , ,

## 7 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”

2. Good post. I learn something totally new and challenging on websites I stumbleupon every day.
It will always be helpful to read through articles from other authors and
practice something from their web sites.

3. Hyodio says:

tnx a lot for this tnx a lot for this very helpful! acpreaipte your help with the below two fold question:a) what if my worksheet names are not as consistent as in your example ? i.e. instead of region 1, 2, 3 I have alphanum codes such as AB1, DB2, CC3 for worksheet names. is there wild﻿ card’ variable make excel look in the next sheet irrespective of its name? b) is there a way to tell excel which is the first worksheet to vlookup into and which is the last ?

4. carinsurance says:

Me dull. You smart. That’s just what I needed.