The VLOOKUP formula may be one of the most helpful tools in an SEO’s arsenal. With so many SEO tools, exports, and spreadsheets required for us to do our jobs effectively, the ability to combine data quickly and easily is crucial. Perhaps you’ve already used VLOOKUP for combining data sets (e.g. “I have one Google Analytics export with page analytics and another export with keyword ranking information. I sure wish I could see it all at once. Voila! VLOOKUP”).
This blog will explain how to take VLOOKUP (and, in some cases, in combination with IF statements) one step further by performing quality assurance (or “QAing”) on metadata, mapping redirects, and QAing redirects.
What is VLOOKUP? VLOOKUP Basics
Before we get into VLOOKUP strategies for QAing SEO optimizations, we’ll do a quick refresher on how VLOOKUPs work in their simplest form. VLOOKUP is a formula within Excel for combining different data sets. Like the GA and Ahrefs example above, say you have a list of URLs and their page analytics exported from Google Analytics. Then, let’s say you’ve exported the number of ranking keywords by URL (hint: a good way to get this if you don’t have a tool that exports that by default — I don’t — is to export all ranking keywords from a tool such as Ahrefs or SEMrush, list each URL on a separate tab, then use a COUNTIF statement to count how many times each URL shows up on the bulk keyword export). You want to get each URL’s number of ranking keywords next to each URL’s page analytics in the same sheet:
You can easily set up a VLOOKUP to combine the data.
The Parts of a VLOOKUP Formula
VLOOKUP looks like this:
=VLOOKUP(lookup_value,table_array,col_index_number,range_lookup)
So, what does each part mean?
- Lookup Value: This is the common element between two sheets. So, in our simple example above, this would be the URL. So, if we’re combining the data in Sheet 1, our lookup value would be “/page1”, which is in A3. So, our VLOOKUP looks like this so far: =VLOOKUP(A3,
- Table Array: The range we want to look for A3 and the VLOOKUP value (number of ranking keywords). This range must include the column that URL will be in (column A) and the column that our desired value (number of ranking keywords) is in (column B). So, that would be Columns A and B in Sheet 2. So, it’d look like this: ‘Sheet2’!A:B. Don’t worry, Excel automatically populates the ‘Sheet 2’ portion based on what the sheet is named, as long as you’re selecting your VLOOKUP values with clicks instead of typing them out. So, our VLOOKUP now looks like this: =VLOOKUP(A3,’Sheet2’!A:B’,
- Col Index Number: The column number of the data you want to pull in, out of the columns you chose for the table array. In this example, we’re trying to pull in each URL’s number of ranking keywords from sheet 2, which is in column B. So, since we chose columns A & B in the previous VLOOKUP element, this would be column number 2, since B is the second column out of columns A and B. So, our VLOOKUP looks like this: =VLOOKUP(A3,’Sheet2’!A:B’,2
- Range Lookup: For this, you either use “TRUE” or “FALSE”. Putting TRUE means that our VLOOKUP will look for the closest match if an exact match does not exist. FALSE means it will return an error if there isn’t an exact match. I’ve never used TRUE in any of these use cases because we’re looking for exact matches.
So, our VLOOKUP in action would look like:
And, once we hit enter and drag the formula down, we get this:
Page 3 gets an error (#N/A) because it doesn’t exist on sheet 2.
Scenario 1: Using VLOOKUP to QA a Site’s New Metadata Implementation
Ok, you got a quick refresher on how VLOOKUPs work. Let’s take it a step further to QA the implementation of some optimized metadata.
Let’s say we’ve mapped out a metadata refresh in an Excel file. We’ll refer to the file/project as “Metadata Refresh”. Our columns are:
- URL
- New Title
- New Description
- New H1
Let’s pretend the site’s huge and that someone else, such as the development team, is in charge of implementing the metadata. But, it’s up to you to ensure that the metadata was implemented correctly. You could just trust the accuracy of your development team, you could manually check each page, or you could combine a crawl of the website with your mapped-out metadata in Excel to verify. I’ve done all three and have consistently had better luck with QAing the work myself. No shade to any development teams I’ve worked with, they’re all brilliant. it’s just easy to overlook metadata optimizations when building an entire site.
To do that, paste a Screaming Frog, Botify, or other crawl export into a new sheet on the Metadata Refresh file, Sheet2. The quickest, but slightly uglier way would be to simply use a VLOOKUP to pull the titles, descriptions, and H1s from the Screaming Frog crawl into the first sheet, right next to their corresponding new metadata. Then, use a simple IF (If new title = live title, return “Match”, if it doesn’t, return “Don’t Match”) statement to see if each piece of metadata is the same. It would look like this:
So, in the end, it would look like this:
Simple, right? This has saved me countless hours of QA, especially within industries where once something’s been approved, it must be on the site, such as pharma.
Making it Prettier
Let’s say you want to make this more client-friendly and cut back on all the columns. You could do this by not pulling the live titles, descriptions, and H1s into the first sheet, but still comparing data with the VLOOKUP. Nest your VLOOKUP within an IF statement like this:
=if(vlookup(A2,‘Sheet 2!A:B,2,false)=A2,“Match”,“Don’t Match”)
In this example, the VLOOKUP serves as the first “IF” element.
This method is much prettier, but is potentially less helpful to you, the SEO, because when something doesn’t match, you don’t have the two titles side by side to compare.
Limitations
Using an “IF equals” statement comes with its frustrations because the statement is looking for an exact match. So, if for any reason your Excel metadata doesn’t match the live metadata, such as an extra space at the end of your Excel metadata but not your live metadata (some content management systems trim your metadata for you), you’ll get a negative response. So, it’s important to manually check all the Don’t Match values before raising a red flag.
Scenario 2: Making Redirects Easier
One of the biggest frustrations I’ve had when mapping out 301 redirects is that things change – in this case, URLs. I can’t count the number of times I’ve had a very robust redirect plan mapped out when suddenly we get a URL change and I have to go through and update all the new URLs in the redirect mapping. Sure, a Find and Replace can knock this out in one fell swoop, but that has its limitations. Luckily, if you base your redirect mapping off of a VLOOKUP, you can make URL changes much easier.
First, what do I mean by redirect mapping? In its simplest form, my redirect mapping documents are Excel documents with the old URL in column A and the new URL in column B. So, how do you use a VLOOKUP to help map redirects?
First step: Name your pages in a sitemap-like sheet. I like to do this when mapping out a new site anyway, so I often have a sheet that looks like this:
Sheet 1:
Easy peasy.
Now, on another sheet with your old URLs that will be redirected, rather than pasting in the New URLs and then risking having to go back and edit down the road when/if URLs change, just use a VLOOKUP formula that pulls in the New URL based off of the Page Name. The Page Name is a customized field where you can assign easy-to-remember names for all of your pages to easily locate that page in any other spreadsheet. You can even consider pulling in the H1 of the page to speed up this process.
Sheet 2:
So, once you’ve gone through and named your pages according to the first sheet in column A, you’ll get a lovely result:
Sheet 2:
Then, down the road, if a URL changes, you change the URLs in the first sheet where you’ve mapped out new URLs, and the redirect sheet changes. This might seem overly complicated for such a simple task, but this method comes in handy if you’ve got a big site where there will be multiple old URLs going to the same place.
Scenario 3: QAing Redirects
We can QA redirects using the same theory behind the metadata QA from Scenario 1. Paste a crawl of the old URLs which includes “Redirect URL” (Screaming Frog and Botify both do this) into a new sheet from Scenario 2 and use a VLOOKUP formula to pull in the redirect URL into Sheet 2. Then, use an IF statement to verify that the true redirect URL matches the URL you mapped out, like this:
Conclusion
Once you’re feeling comfortable with VLOOKUP formulas, the possibilities for using it to make your job easier are endless. Hopefully, you can take these three simple methods to 1) make your job easier, and 2) expand upon them to do even better things.