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):
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:
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.