Lesson 4: VLOOKUP, OFFSET, INDEX/MATCH
Here in lesson 4 we’ll be talking about some lookup and reference functions. These guys are great for matching up data between two sources, for example, combining keyword volume (data from the Google Keyword Tool) with current rankings (data from your rank checker of choice). In fact, this is the exact example we’ll use to demonstrate our first function…
Microsoft Excel Defininition: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.
Combining search volume and current ranking of a keyword list is a great way to prioritize upcoming SEO efforts, and can be done at any stage in a website’s life. VLOOKUP makes this a painless process. With our targeted keyword list in hand, we head over to the Google keyword tool to grab search volumes.
After cleaning up the output from the GKT, we’ve got our list of keywords and search volume
Next we’ll run rankings for our site using whichever ranking tool we choose, and export that into Excel.
What’s that they say about a company not eating their own brand of dog food? Yeesh.
After turning our ranges into tables named “Volume” and “Rank” we can combine our two datasets into one pretty table with VLOOKUP.
In our “Volume” table, we’ve created a new column and used the following formula to pull in the appropriate data from the “Rank” table.
In plain English:
Find this exact (range_lookup) keyword (lookup_value) in the “Rank” table (table_array), and return what is in the second column (col_index_num) of that “Rank” table. Remember, in that “Rank” table, the actual rank is in the 2nd column.
The [range_lookup] portion of the VLOOKUP function is an optional value that specifies whether we want to find an exact match or an approximate match. If range_lookup is TRUE or omitted (for an approximate match), the values in the first column of table_array must be sorted in ascending order. If range_lookup is FALSE (for an exact match), the table_array does not need to be sorted.
In my personal SEO + Excel history, I’ve never set range_lookup to TRUE. In other words, I’m really only ever looking for an exact match. This simplifies things when it comes to that nasty ascending sort order requirement. If you see a bunch of #N/As when using VLOOKUP, check to see if your range_lookup is either omitted or set to TRUE.
This function is another doozy that I end up using over and over for one specific SEO task – Analyzing compared date ranges from a Google Analytics CSV export. That’s not to say this function is a one trick pony; I’d be surprised if you don’t find a need for it once it’s in your toolbox.
Microsoft Excel Definition: Returns a reference to a range that is a given number of rows and columns from a given reference.
After cleaning out the extraneous data we have our compared date range data from Google Analytics. In particular, we have referring keyword traffic from one week compared to the previous. Now, if we’re just comparing these 4 (gibberish) keywords we’d be ok, but since we’re actually looking at thousands of keywords it won’t be easy to see through the clutter.
Using OFFSET we can turn the above, into this:
Getting our first row of ‘Keyword’, ‘Week 1′, and ‘Week 2′ is simple enough, but how can we get Excel to populate the rest? Let’s take a look at the formula:
Let’s break the formula down piece by piece, starting with how we’re relocating our ‘Keyword’ column.
ROW is a simple formula that just returns the number of the row a cell happens to be. You’ll notice that only the column (A) is set as an absolute reference. We want the row to rise as we move down each row.
In our example, OFFSET outputs the cell that comes 4 rows beyond the reference point.
The next time the row is calculated, our references will incrementally rise, allowing the formula to adjust according to our raw data.
With some fancy math, we’ve been able to make the row value jump 4 each time the formula moves to the next row. For our ‘Week 1′ and ‘Week 2′ columns, the formula has changed slightly, but the basic premise remains the same.
INDEX/MATCH is like a more powerful version of VLOOKUP, but it is a bit harder to wrap one’s head around. In most cases they are interchangeable, but where VLOOKUP falls short, INDEX/MATCH gets the job done.
The biggest issue with VLOOKUP lies in the first few words of the Microsoft Excel definition:
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
In instances where the key field is not in the left-most field, VLOOKUP will not work. Let’s say we have a list of the referring keywords of the past 90 days from Google Analytics. For budgeting and reporting purposes, we’ve classified each keyword as branded, head, mid, or long-tail in a “Category” column in the left-most position of our table.
If we were attempting to budget traffic for the next 90 days on another sheet, VLOOKUP would disappoint:
Now, we could certainly go back and edit our source table to make VLOOKUP work by moving our keyword column to the left-most column, but INDEX/MATCH is really all we need.
Let’s first cover what INDEX and MATCH actually do when used alone.
Microsoft Excel Defintion:
INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
MATCH: Returns the relative position of an item in an array that matches a specificied value in a specified order.
Both of these functions are actually quite simple alone, but do something really powerful when used together. The beauty of Excel!
With INDEX, Excel simply returns the value that is found at the intersection of the row and column you’ve supplied. For example:
Return the value at the intersection of the 3rd row and the 4th column. The value returned is “16″.
With MATCH, we can find the relative row that matches our lookup_value. For example:
Find the row in which “14″ is found within this single colum. The value of “3″ is returned.
Moving back to our earlier example, we can combine INDEX and MATCH to pick up the slack where VLOOKUP fails.
In plain English: Return the value found at the intersection of the 4th column and the row where the given lookup_value can be found. For the first row of our table: Find “seo” in the Keyword column of the Keyword_Table and report its row (this is the MATCH portion). Now, return the value found in the 4th column of that table’s row (this is the INDEX portion).
There you have it! Sure, VLOOKUP gets the job done 90% of the time, but INDEX/MATCH does 100% of the time, so why not master the one that works all the time!
You’re getting Oh So Close to becoming a certified Excel Ninja! For completing this lesson, you’ve been awarded this completely awesome ninja sword!