Why we all like "Index" so much

We have a new club in the office.?We've called it a club because that makes what we're doing sound more interesting, but?we?all know what it's really about. It's all about helping some of the corporate finance folks?improve in Excel.?Who wouldn't want to be part of that club?

I don't think I can say that all the members are much younger than me because we're not allowed to talk about that sort of thing any more. But I reckon I've been using Excel at least 20 years longer than the club member average. When a bunch of us jumped in a small VW to go to the pub for lunch the other day I complained about Radio 1 as the choice of station. "Oh yeah my Dad likes Radio 2 as well" [multiple sniggers]. I think to the rest of them I'm a cross between Excel-Yoda and the embarrassing uncle you don't know which table to put on at family weddings. It's a position I'm perfectly happy to occupy.

The club is a lot of fun. If you like Excel.

Index as a data picking solution

In response to my question "What Excel functions would you like help with?" "Index" seemed close to the top of the everyone's list.?They've seen it in other people's models and they know other people like it.

"What a great function to tackle!" I thought. There are some sound reasons for Index's popularity.

There are lots of data picking solutions out there

Imagine you've got a big table of Excel data and you've got to pick?out the data point you're interested in. It's a common Excel challenge. You could have a set of budget numbers and you might be wanting to call the latest results out of your annual budget so you can compare them against latest month actuals. You could have a big table detailing capital spending plans with time period across the top and asset class down the side. In your model you need to fish out the correct piece of data and slot it into its place. You need a data picking solution.

Most of "the club" know about Vlookup and some of them have heard of or seen other solutions such as Offset or Hlookup or SumIf. The point is there are lots of 'picking' solutions out there. But the other point is: you only need one. I think my corporate finance friends had worked out that?serious modellers seem to be fans of Index.

I want Index to be my solution

Let's take a quick look at how Index works. This is the example we worked through at "Club" (if you say it without "the" in front of it and capitalise it, it becomes even cooler).

Separately to "Club" we have a quiz running in the office right now - so that?became our specimen table of data. We could have used budget or capital spending numbers but using all the office team quiz results for our Index-building exercise somehow seemed more interesting.

What?did the "Participate" team score in week 3?

Maybe one of the reasons people like Index is that it's not too hard to get your head around. You point Index to a table of data. Tell it the row number and the column number and it will fish out the piece of data you are interested in.

In this case week 3 sits in row 3 of the table (assuming the table starts in cell F4). Participate sits in column 5. Index looks at the intersection of row 3 and column 5 (in the table F4:O7) and picks out the correct value: 11.

The role of Match

Some of my colleagues had come across Match combined with or embedded inside Index. That's when things can start to look a bit complicated. As in the example, it could sometimes help to separate out the Match functions into their own boxes so that it's clear what each of them is doing. You know now though: they're just being used to help us determine the row number or the column number. Cell?B7 is working out what row number week 3 is sitting in. Cell?B8 is working out what column number Participate is sitting in. Even in the extreme embedded case (two Matches inside an Index) you've got a good idea what might be going on. The Match functions are there just to give Index the row and column information it needs. We don't like long formulas do we? So sometimes it can make things a bit clearer if you try and separate them out into their own boxes, as in this example.

Index's magic trick

Index's slightly-impressive magic trick is that it doesn't need all of its inputs to work. So, if you've got a list of data, just point Index to the list and give it the row input and it will do its magic.

Similarly, if you point Index to a long single row of data and just give it the column input, Index will chew on that information quite happily and deliver you a result. You can see that going on in cell B14 where I've asked Index to pick out the 10th team along the heading that contains the team names.

You can also see Index's magic trick (i.e. the fact it doesn't need all the inputs to work) applied in cells B17:B20 when I've decided that I want to dump out all of Participate's data for all weeks. I've set the Index up in the first row (using some careful $ row locking) and then just filled the formula down. As a result, the Index formula that's giving us all that data is very simple - it only requires the column input.

You can see why pro modellers and pro models start to gravitate towards Index. It's relatively simple to understand. You can use it to pick data points out of tables, lists or rows. It's nice and simple!

Another big reason to love Index: it's less memory hungry

Most regular Excel users I've bumped into have tended to be more familiar with another picking solution apart from Index. Perhaps something like Vlookup. For many years I had been told that Index used less memory than Vlookup but hadn't seen it as a real issue in the models I was working with. That was until someone came in one day with a big spreadsheet containing?huge quantities of data but with MB size much larger than I thought normal. There were a couple of clear mistakes being made. For one the spreadsheet contained lots of formulas looking all the way to the bottom (a million or so rows). For another it contained, you guessed it, acres of Vlookups. After fixing just those two problems, and replacing Vlookups with Index, spreadsheet size reduced and the computer sped up dramatically. It was a lesson to me. Working on the basis that Index really is less memory hungry than Vlookup, why not?stick to?that one?

Don't be scared. You only need one. It can be Index

So there's no need to get intimidated by the many solutions Excel offers us for plucking data out of a table (Vlookups, Offset, Hlookup, SumIf - I've even seen people try and do it with an "If"). Just remember, from all appearances, pro model builders seem to love Index. Every professionally-build model I've come across seems to make liberal use of Index. So if you're looking for a reason to alight on Index, that's a big?incentive to stick with it just there: the pros love it. In any case, we really only need to be confident with one data picking solution to survive our financial modelling careers. Why not make Index your one?