TL;DR

=VLOOKUP(A2,B:B,1,0)

This will look in cell A2 and return the value of that cell if it is found in column B.

 

 

VLOOKUP with Google Analytics SEO Example

Did this the other day, well Mark did…

 

So we were given a list of URLs to find the amount of pageviews for –

– Have the URLs you need to find pageviews for listed in an excel sheet, then…do the following:

1.  Export this report from Google Analytics (GA):

2. Show – as many rows as you need to, so that all the data is displayed in the table. Use the drop down menu at the bottom right of the table:

3. Export as CSV (use Export drop down near top of page)

4. Copy and paste the CSV data into the second tab of your original excel spreadsheet with your URLs on:

 

5. Use a vlookup, to match your original list of URLs, with the GA report:

when looking at the vlookup formula, remember that the tabs on my spreadsheet were called “URLs” and “Analytics All Web Site Data Pag”

Here is the breakdown of the VLookup syntax to help you to understand what is going on here:

 

It basically tells Excel to find the URL in the cell A1 on the first tab, in the table on the second tab, return a value from the second column of that table when the URL is matched.  The 0 just means make sure it’s an exact match

 

Remember, vlookup can only look left to right. so if pageview had been in the first column, and URLs in the first, we would have had to copied and pasted the table data around, or used INDEX & MATCH

 

– please note, my new awesome screenshot for chrome plugin stupped working after the first screenshot from GA, so I couldn’t do nice arrows and annotations.

 

 

 

Partial Vlookup