Diễn Đàn SEO Panda - SEO Panda Forum

Diễn Đàn SEO Panda Dành Cho Các SEOers Tự Do Thảo Luận SEO - SEO Panda Forum - Free SEO Forum to share your knowledge to the world
 
HomeCalendarFAQSearchMemberlistUsergroupsRegisterLog in
Search
 
 

Display results as :
 
Rechercher Advanced Search
Latest topics

Share | 
 

 Using VLOOKUP to Match Keyword Volume & Rankings Data

View previous topic View next topic Go down 
AuthorMessage
khiemsound



Posts : 1016
Points : 11605
Join date : 2012-03-27

PostSubject: Using VLOOKUP to Match Keyword Volume & Rankings Data   Tue Apr 17, 2012 9:11 am

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!
Back to top Go down
View user profile
 
Using VLOOKUP to Match Keyword Volume & Rankings Data
View previous topic View next topic Back to top 
Page 1 of 1
 Similar topics
-
» Page load issue
» WEST MEMPHIS THREE FREED AFTER PLEA DEAL
» The Best Game On The Wii Shop Channel Round 1 Match 3
» Tips Merawat Ikan Mas Koki
» DataDriven from Excel in C# with NUnit

Permissions in this forum:You cannot reply to topics in this forum
Diễn Đàn SEO Panda - SEO Panda Forum :: Search Engine Optimization :: SEO Tools & SEO Tips-
Jump to: