Using VLOOKUP to Match Keyword Volume & Rankings Data

Ever needed to compare lists of keywords in different data tables and match corresponding values together? For example, matching keyword volume data to search engine rankings? Today we’re going to take a look at a really simple but powerful query in Microsoft Excel called VLOOKUP which makes that possible.

Here’s the definition of VLOOKUP from office.microsoft.com:

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

In plain english that means you can use VLOOKUP to match two values and return a bit of data in a different column but the same row. Maybe Microsoft’s definition was better. Here’s how to do it:

1) Import your data from your rankings tool and Google Keyword Tool. I’ve created two tabs in Excel, “Google Keywords” and “Rankings”.

2) In my Google Keywords tab I create a new column, titled “Google Ranking”

3) Now I’m going to write my simple vlookup query. Here’s how it looks:

=VLOOKUP(A2,Rankings!$A$1:$B$151,2,0)

The query works by looking at cell A2 and matching that value in a predefined area of the spreadsheet called a table array. In our case, the table array is Rankings!$A$1:$B$151. “Rankings!” refers to data in the other tab, and $A$1:$B$151 describes our table array. Think of the “$” as an anchor point, so, when you drag the query across a number of cells (A2,A3,A4 etc) the table array won’t move. In short, with a “$” we’re always looking at the same area of data to do our matching.

The “2″ means the 2nd column from the leftmost point in our table array. That’s the ranking position data in our second tab. Finally, the “0″ means exact match, which is the only sensible option when you’re matching words rather than numbers.

Once you understand how the query works it’s reasonably easy to make it more complex. Maybe you could consider adding data from other sources such as Hitwise and MSN Ad-centre Intelligence? Finally, I’ve uploaded my example spreadsheet here for you to download and inspect. Enjoy!

Using VLOOKUP to Match Keyword Volume & Rankings Data, 5.0 out of 5 based on 2 ratings

Comments

  1. Lodewijk

    Hi Richard,

    Nice post, never thought of a tool like this.. Have been playing around with the keyword tool on googles site, since I was getting all these “new ideas” for keywords and not a fixed list like you have..

    Now I know how you got that fixed keyword list(simply put that filter on), so you can run this every week or so..

    So if you sign in @ Adwords you can save these keywords and run this same list over and over?

    I use the rank checker of SEO Book, but the tools does not make the layout like yours does.. which one are you using?

    And dont you think it’s easier if you do see the url the keyword is ranking for?

    Keep it up!

  2. David Carralon

    For anyone reading this post + the comments from Lodewijk, just to add that WebCEO does also give you the ranking url in your website for each of your rankings, so you can benefit from the vlookup tip Richard is giving us on top of what you get out of WebCEO or Advanced Ranking… it’s funny that I keep coming back to this post to pick up the liner: =VLOOKUP(A2;Rankings!$A$1:$B$113;2;0) instead of having it handy or learning it at once, : ) thanks Richard