Diễn Đàn SEO Panda - SEO Panda Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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
 
HomeLatest imagesSearchRegisterLog in
Search
 
 

Display results as :
 
Rechercher Advanced Search
Latest topics
» Top 10 SEO Tools ( fresh links )
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyWed Feb 04, 2015 10:56 pm by tranvanchienhn

» Bán Textlink Pr4-Pr6 (1PR9)
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyWed Feb 04, 2015 10:54 pm by tranvanchienhn

» Google’s Penguin Update: What it is, What it isn’t, and What to Do
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyTue Apr 22, 2014 3:23 pm by johnaclayton

» Free Click Bank Affiliate Tool
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyTue Apr 22, 2014 3:21 pm by johnaclayton

» SEO is Dead – Long Live SEO
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyTue Apr 22, 2014 3:17 pm by johnaclayton

» Local SEO là gì - 10 Thủ thuật Local SEO
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyWed Apr 02, 2014 3:31 pm by bao.seoinet

» Get High Rankings by Building Authoritative, Irrelevant links?
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptySat Sep 21, 2013 9:32 pm by kyostyle1

» The best Engineering software
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptySat Sep 14, 2013 2:12 pm by read2

» Tuxedos- Traditional Interest Trend
Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyWed Sep 11, 2013 2:16 pm by taiiwin


 

 Using VLOOKUP to Match Keyword Volume & Rankings Data

Go down 
AuthorMessage
khiemsound




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

Using VLOOKUP to Match Keyword Volume & Rankings Data Empty
PostSubject: Using VLOOKUP to Match Keyword Volume & Rankings Data   Using VLOOKUP to Match Keyword Volume & Rankings Data EmptyTue 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
 
Using VLOOKUP to Match Keyword Volume & Rankings Data
Back to top 
Page 1 of 1
 Similar topics
-
» Google Keyword Tool – Volume Data is Permanent
» How to calculate approximate traffic volume for the past 12 months in Google Keyword Tool
» Http Status Reports With Open Site Explorer, Xenu and VLookup
» Fresh To Network Marketing? Look At This Data!
» New samsung S5260 Evaluation Data

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: