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:
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!
A 2017 Update
VLOOKUP remains one of our most-used Excel tools at Builtvisible. For a more detailed introduction, check out this VLOOKUP tutorial from Spreadsheeto.