How to Use VLOOKUP in SEO

Microsoft Excel's VLOOKUP can be used to match keywords with search engine rankings.

A website owner uses keyword reports to develop the web publishing strategies that improve his site's standing in search engine rankings. This process is known as search engine optimization. Microsoft Excel's VLOOKUP tool allows website owners to compare different keyword performance data sets. This allows the owner to visualize the impact of targeting these keywords with his SEO strategies.

Instructions

1. Search for and export the data for your target keywords. Use the Google AdWords tool to search for the keywords germane to your website. This data will show the number of times users search for each keyword on a monthly basis. Click the Google Keyword's download button, and export the keyword list into an Excel file.

2. Import the keyword data into a new Microsoft Excel tab. Name the tab Keywords.

3. Import the comparison data into a second Microsoft Excel tab. Name the tab Comparison. The comparison tab can include any set of data you wish to compare to the keywords report. For instance, you could compare search engine ranking data for a set of keywords, with the number of times a user searches for those keywords.

4. Create a new column of cells in the Keyword tab. This column will contain the VLOOKUP function that will display the comparison data for each keyword.

5. Insert the VLOOKUP formula into each new cell. Then insert the comparison data's array into the formula. Place the upper left-hand corner's cell ID as the starting cell and the lower right-hand cell's ID as the ending cell. In the following example, the VLOOKUP formula instructs Excel to compare the data in the Keyword tab's A-2 cell with with the data in the Comparison tab's cell A-1 through B-10. The dollar sign in front of the cell number serves as an anchor, if you need to copy and paste the VLOOKUP formula into other cells.

   =(VLOOKUP(A2,Comparison!$A$1:$C$10)

6. Populate the VLOOKUP formula with the comparison data's target column number. This data will appear in the Keyword tab's cell where the VLOOKUP formula lies. The example now dictates that the data from the second column, or the B column, will be copied from the Comparison tab and placed into the Keyword tab when the formula's criteria are met.

   =(VLOOKUP(A2,Comparison!$A$1:$C$10,2)

7. Set the exact match parameter. In this final version of the example, the number zero has been used as the exact match parameter. This requires the data in the comparison data's B column to match the data in the keyword tab's A-2 cell.

   =(VLOOKUP(A2,Comparison!$A$1:$C$10,2,0)

References

• SEO Gadget; "Using VLOOKUP to match keyword volume and rankings data"; Richard Baxter, November 21, 2008
• Google AdWords: Keywords Tools
• Geeks Desk: How to use VLookup in Microsoft Office Excel