Formula Friday #4

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

I like to think of today’s formula as a step above the VLOOKUP. And I don’t mean any disrespect to the VLOOKUP. It’s just this formula changes the game. It’s a little less old school – slow but effective- and a lot more new school – fast and flashy.

Today we are combining the INDEX and MATCH Functions

We are going to dive straight in with the flashiest way to combine the INDEX and MATCH Functions, INDEX MATCH MATCH. In my opinion, learning this way first, makes it easier to get your head around an INDEX MATCH formula.

INDEX MATCH MATCH formulas can be used when your data is in a grid (matrix) format. What sets it apart from a VLOOKUP is that the lookup value doesn’t have to be in the leftmost column. And, you don’t need to know the number of the column you want to return the data from.

On with the show

We’re going to start off with our data in a familiar format. Our week numbers are down the side and our KPIs are across the top.

Index match match data table example

This particular example is looking at email performance. But could just as easily be a summarised WSSI. Or a tracker to show your AdSpend performance.

It’s likely you will need to pull a topline performance summary from a document like this. Something that shows how your main KPIs performed for a particular week. Maybe for Monday trade reporting or end of month performance reviews, for example.

In which case we would create a summary tab which looks a little like this.

Index match match summary table

A format that is both printer friendly, and copy & paste-able into a summary email.

Whenever you create a summary document you’ve got to think about who is going to read it, and what it is going to be used for. The key element of a data summary – in my opinion – is to be informative enough to give clear direction on what actions to take next. This will require you to be aware of what your KPIs are and how they relate to each other.

And please remember to format your document. Formatting can really help to guide the reader, and help them come to the same, or similar, conclusion as you. If you didn’t already know, I am big on formatting. BIG on formatting.

Bringing it back to the real reason you’re here

First things first, we need to know what we want to do. For this example, we want to pull in data from the Performance Tracker tab. And create a clear one page summary, on the Summary tab, to show how week 39 performed.

The “foundation formula” – let’s say – used to populate the above table is this:

=INDEX(‘Performance Tracker’!$A$3:$AR$55,MATCH(Summary!$A$2,’Performance Tracker’!$A$3:$A$55,0),MATCH(Summary!B$3,‘Performance Tracker’!$A$3:$AR$3,0))

Two additional formulas have been included as well. Which are slight variations of the above formula. And we will come onto those a little later.

To start things off you need to select, or INDEX, the reference area – technical term array. It’s the place where Excel can find the answer to your MATCH criteria.

=INDEX(‘Performance Tracker’!$A$3:$AR$55

In this example we are selecting the entire data table on the Performance Tracker tab.

Index match match data table example

Next you want to set your first MATCH criteria.

Identify your lookup value. In this example it’s the week number in cell A2 on the Summary tab.

Index Match Match week number

Then select the place Excel needs to look to find a match for this first criteria.

For the formula to work the match will need to be found in a column of your original array – reference area.

=INDEX(‘Performance Tracker’!$A$3:$AR$55,MATCH(Summary!$A$2,‘Performance Tracker’!$A$3:$A$55,0)

In this example a match for our lookup value can be found, in column range A3:A55 on the Performance Tracker tab.

Index Match Match column match

You will close off this formula by setting your second MATCH criteria.

You will need to identify your lookup value. In this example the lookup value is the name of the KPI. For the formula to work, the place Excel needs to look to find a match for this second match criteria will need to be the topmost row of your original array – reference area.

=INDEX(‘Performance Tracker’!$A$3:$AR$55,MATCH(Summary!$A$2,’Performance Tracker’!$A$3:$A$55,0),MATCH(Summary!B$3,‘Performance Tracker’!$A$3:$AR$3,0))

In this example the lookup value is found in cell B3 on the Summary tab – “Demand”. Which is a match to the relevant column title found in row range A3:AR3 of the original reference area.

Index Match Match row reference

How does an INDEX MATCH MATCH actually work?

What I like the most about an INDEX MATCH MATCH is that it is a very flexible formula. Once you have set up your foundation formula, and made sure we have locked in ($) the relevant data ranges. You can copy and paste to your hearts content. With the only adjustment needed to ensure the correct KPI is selected in our second match criteria.

And this is because the formula works on very similar principles as to how your math teacher would have taught you to read a graph at school. You know – the whole go along the corridor (x-axis) and then up the stairs (y-axis) to find the value. It’s the same with this formula.

We have to tell Excel what column to look in first so it can identify the row (corridor) to look at. And then we need to tell Excel what row to reference, so it can identify which column (staircase) has the meeting point.

Index Match Match direction example

This image shows what the result would be for opens in week 39.

So as long as we can find a match for our set criteria we don’t have to worry about counting column numbers, like we would if we were using a VLOOKUP.

The eagle-eyed among you might be wondering how I pulled in the LW and LW-1 data

“Hey Andrea, how simple can it really be when you have several columns that say vs LW on the Performance Tracker tab?” Well question asker, firstly good question. Extra brownie points for attention to detail. Secondly, we add to the formula of course. They – meaning me – don’t call me an Excel Wizard for no reason.

I mentioned earlier, there were two slight variations of the foundation formula included in the summary table. These variations exist for the “vs LW” and “vs LW-1” data.

I’m sure you are familiar with the variance formula. My preferred way of calculating it is

=(current value/previous value)-1

Some people like to calculate it a different way. A way I always struggle to get my head around. It’s like writing with your left hand – if you are right handed like me – you know it works but for some reason it just doesn’t look right.

We already have our current value. This value is housed in cell B4 on the Summary tab. Delivered to us with thanks to the foundation formula we set up at the beginning. So the only thing we need to find to make this variance formula work is the previous value.

To get the previous value, for our variance formula

We can make one amendment to the foundation formula. The amendment is made in the first MATCH criteria, so that we can provide Excel with a different lookup value to reference in column range A3:A55 on the Performance Tracker tab.

So the formula ends up looking like this for vs LW.

Amendment highlighted below

=(B4/INDEX(‘Performance Tracker’!$A$3:$AR$55,MATCH((Summary!$A$2-1),‘Performance Tracker’!$A$3:$A$55,0),MATCH(Summary!B$3,‘Performance Tracker’!$A$3:$AR$3,0)))-1

And looks like this, for vs LW-1.

Amendment highlighted below.

=(B4/INDEX(‘Performance Tracker’!$A$3:$AR$55,MATCH((Summary!$A$2-2),‘Performance Tracker’!$A$3:$A$55,0),MATCH(Summary!B$3,‘Performance Tracker’!$A$3:$AR$3,0)))-1

In the above examples we are changing the week number used as the lookup value for our first MATCH criteria. By adding in a formula to subtract from the current week number in cell A2 on the Summary tab.

For last week (LW) we minus 1, for LW-1 we minus 2, LW-2 we would minus 3 and so on.

You can also combine the INDEX Function with a single MATCH. Which we will cover another time. I like to think of an INDEX MATCH as a cross between a SUMIF and a VLOOKUP.

In an INDEX MATCH formula, your array becomes a single column. And much like a SUMIF, your match result is found in a single column. But unlike a SUMIF, if there are multiple matches in your match column. It won’t add the values together, it will just return the first value it finds, like a VLOOKUP.

But like I said that is for another time.

How did I do?

I’m pretty sure that was longer than a five minute read. But time well spent I’d say. I’m sure this will be one of those formulas you’ll start using more and more as time goes on. It does take a bit of practice to get used to, so if you have any problems just drop me a line.

Until next time,


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.