You would be hard-pressed to find an SEO specialist that has never used VLOOKUP. Nowadays we have access to a massive amount of data from various tools, and the most powerful SEO analysis can be achieved when we connect all these data points, which often means looking up values from different sources.
In this post, we want to give you examples on how and when you can use INDEX MATCH and its more powerful version INDEX MATCH MATCH as an alternative to VLOOKUP.
VLOOKUP is a great formula, but it does have its limits. The main one being that it will only work when it’s pulling data in a left to right fashion. This is where INDEX MATCH comes in. This formula has the freedom to pull from right to left as well as left to right.
An important thing to note is that by switching the columns around in the master sheet we can complete this task with VLOOKUP, however, doing this may break other formulas tied to those cells. Plus, it’s always easier to work with the original output format of a document, enabling you to paste new values into the document without breaking the formula.
Here’s an example of how we’ve used it recently. We were given a list of product numbers, with which we needed to find the associated keyword.
This was the spreadsheet we had to work with in order to find the data we were looking for, and as you can see it contains both the Product No. and the Keywords we are looking for. A VLOOKUP will not work here as we have a list of product numbers and need to find the keywords associated (which are further left than the product numbers).
First things first, we created a column for the Product Numbers which we have on hand. Then we go about setting up the formula. The formula should be placed in the first empty cell in the “Keyword” column of the second table in the image below (cell C14).
The formula is as follows:
INDEX (Range of cells that you want to find values from),
MATCH (Cell you want to find the corresponding value of – range of cells from which the previous cell value is from, 0)
Once the formula has been entered, you can then drag it downwards to the bottom of the table, allowing you to put in your Product No. values and have the formula pull the keyword automatically.
Now that we know why and when we should use INDEX MATCH instead of VLOOKUP, we can look at a more powerful Excel lookup formula combination which is INDEX MATCH MATCH.
How can it be useful for SEO?
“Internal – All – Export” – a regular Screaming Frog user can easily recognise these steps for exporting crawl data. We all know what a great tool it is and cannot imagine our work without it. But sometimes we wish it could only give us the data we need depending on what metrics we want to look up. And this where INDEX MATCH MATCH can come very handy.
The number of columns in the exported spreadsheet from the Internal tab can vary depending on the number of that were found on the site (rarely, but sometimes you can see sites with two titles added to their pages, or even two canonical tags), and if any additional data is included or not (e.g.: Google Search Console and Google Analytics).
INDEX MATCH MATCH allows to perform two-ways look-up – a vertical one (to find certain URLs among all URLs from the very left “Address” column of the crawl) and a horizontal one (to match data from certain columns in your Screaming Frog crawl for these URLs).
An example will help us to see how it actually works.
The table below represents a Screaming Frog crawl. In this example we want to look up the values from the “Meta Robots” and “Canonical Link Element” columns for URLs 4, 7 and 9 (all marked in orange).
To do so we are using INDEX MATCH MATCH as shown below:
So, how do we actually get to this result using INDEX MATCH MATCH formula combination?
Below you will find the same images as above but with a coloured markup that should help further explain the formula. However, for clarity, let us review it step-by-step.
We added the above formula to the row C16 as per image below (or add it next to the first URL you would want to look up the metric values for):
- INDEX (mark up for your entire crawl),
- MATCH (lookup value – your first URL to look up, the column we want to look up against, 0),
- MATCH (lookup value – your first metric to look up, rows we want to look up against, 0))
As you can see, we are using $ sign in our INDEX MATCH MATCH formula combination, which allows us to lock columns and rows. When added to the formula, as shown above, you can easily drag it down to populate the lookup values for the remaining URLs.
We hope you will give INDEX MATCH MATCH a go as a Vlookup alternative to in your next SEO analysis if you haven’t already done so. Here at Passion Digital we love to share knowledge and learn from our peers, so if you have any other use cases when these formula combinations can be applied for everyday SEO analysis, feel free to get in touch and let us know about them!