## How-To: Manipulating Data in Excel

Posted on Monday, May 16th, 2011 by Silvia Anghel 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:

- You can look up values in any column in the table (VLOOKUP limits you to the left-most column).
- 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:

- How-To: Manipulating Data in Excel - May 16, 2011
- Review of Microsoft Excel 2010 - December 21, 2009

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

“Black Seo Guy “Signing Off”

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.

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 ?

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