Today is is going to be tough. We’re going to learn a super powerful formula structure called INDEX MATCH. We did VLOOKUP in the last class (8), this is more like Barry Bonds on steroids… Well just Barry Bonds, I guess ;).
Intro to Excel model training
This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.
This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.
To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.
You can join up to the training here.
What’s the point of INDEX MATCH?
INDEX MATCH is actually two Excel functions. INDEX and MATCH alone are not very useful, but together they rock!
At a simple level, we’re deciding what we want to look for and find it in a database just like VLOOKUP, but it’s a bit harder to get your head around.
Don’t stress out about Learning how to do INDEX MATCH off by heart. I actually can’t remember how to do it and I have been using it for years (UPDATE: I know now since I had to explain it to you, lol). It’s sad to say but I Google the syntax every time I use this. So I am trying to take the pressure off you. What you need to remember is “this is how you solve complex search queries in Excel“. So when you need to use it Google it 😉
The big reason you want to use INDEX MATCH over VLOOKUP is, VLOOKUP is a like Derrik Zoolander. It can’t go left. INDEX MATCH is far more flexible.
Also, INDEX MATCH runs a lot faster in Excel, so if you are going to do a million formulas, better to use this.
I use VLOOKUP as standard as I’m lazy and like to do the simplest thing. I also find it easier to audit. Sure, it’s technically a bit slower, but I just want results, as do you I’m sure. I use INDEX MATCH to solve problems when I need to. It’s part of the tool kit.
Let me show you what this looks like in the wild. Here are three examples from the 50Folds SaaS model.
Here I pull the annual tax number for the right month from the tax sheet and put it in the P&L forecast
Here I want to add the actual customers to date from the historics to the cohort based forecast
Finally, in this example, I use a series of calculations to automatically select the defined number of months a founder wants to calculate their runway and leave an error message of “Not used.” This is pretty nifty stuff 😉
Have a look at this dataset.
If you want to know who has an ARPU Of 50, you can’t do this with VLOOKUP unless you move the column across to the first column. Ain’t no body got time for that!
You clearly need to do something else, right?
How do you use INDEX MATCH?
The structure of the formula is:
=INDEX(column with data you want,
MATCH(value you are looking for,
column which contains this data,
So write INDEX( then range the column with the data you want to find the answer. Then you write MATCH( type what you are looking for (e.g. ARPU of 50) and then range the column where that value is in. Out will pop “Jim.” Jim is the dirty dog with an ARPU of 50.
You can make this more complicated by saying who has the highest ARPU and replacing the ARPU value of 50 with a MAX() function.
Don’t worry, I haven’t written down the formulas here as they are in the exercise sheet. I want you to try to figure it out first as you will learn better that way.
So what are these formulas doing?
Honestly, don’t bother reading this bit unless you really care. You don’t need to understand, you just need to do it 😉
- INDEX takes your range and returns a cell within that range based on a count you would write
- This would look like INDEX(the range you want, and then 3) So if you have a one column range with three rows it would take the last one. But you don’t want to have to pick the “3”, you want the formula to know the number should be 3…
=MATCH(lookup_value, lookup_range, match_type)
- The MATCH function returns the position of a cell within a range by matching criteria
- MATCH tells INDEX the number 3 automatically. So a bit like VLOOKUP, you pick what you are looking for, then select the range to find it (It’s looking in the first column like VLOOKUP) and then the match type is FALSE, but you write 0 instead.
- So the formula of MATCH(select the range to look, then type 50, and then 0) and out will pop “3”
- See where we are going with this. MATCH tells INDEX 3 and INDEX takes the index number (the row) of 3 = Jim.
This hopefully makes some sense now? It’s just logic.
The excel example sheet will help you to learn this with an exercise.
To get the training model, subscribe to the series below.
Get in the game
Free tools and resources like this shipped to you as they happen.