News and Blog Articles

With Excel sometimes (in fact, often) it seems we are provided with an embarrassment of riches.  There’s often more than one way to achieve your aim in Excel. 

That can make it a bit confusing – it’s hard to know which solution is best for the challenge at hand.

As a general guide (1), something that’s shorter and simpler is going to be better. 

As another general guide (2), you just need something you’re comfortable with that works.  You perhaps don’t need to worry too much about other people’s preferences.

Often in Excel we need to pick a particular piece out of a table of data.  Imagine data with headings across the top of the columns and rows running down the side.  You want an Excel function that will pick out the piece of data that sits in column X and row Y.

There’s a bewildering array of alternatives.  Once (like many) early in my career I was a Vlookup person.  Now, more recently, we have Xlookup which I kind-of love.  And then we have Index (often combined with Match).  But that’s just scratching the surface of the creative solutions people can find to picking out the piece of data that sits at the intersection of column X and row Y.

I’ve noticed that many (most?) long-in-the-tooth pro modellers tend to gravitate towards Index.  I think that’s because they understand how it works, and that often you get a chance to simplify Index a little.

Index will give you the piece of data that sits at column X and row Y. 

But often in modelling you regularly find you want a whole column’s worth of data (e.g. if you have months as your column headings, you might want all the results for that month). 

Or you might find you want a whole row’s worth of data (e.g. you want the row that contains revenue for a set of months). 

In both of those regular-every-day situations, Index – elegantly – provides you with an opportunity to simplify. 

If you want a column’s worth of data, you can construct a simplified Index – and Index will cope.

 If you want every column in a row, a nicely-simplified version of Index will provide you with a whole row’s worth of data too.

There are lots of every day occasions where Index lends itself to simplification, elevating it in the quest to find the winner of the shortest neatest data picking solution in the world.

When you’re handling volumes of data (imagine a whole column’s worth of data or a whole row’s worth of data), if you think carefully about your formula construction, you’ll find you’re unlikely to need to solve the problem of working out which row or column you need inside every single Index function. 

Often you find you can centralise in one cell the row and/or column you need, with every Index function looking to that particular cell (as per the examples you can see in the screen shots above).  The opportunity for centralising the ‘matching work’ reduces the work Excel has to do.

Don’t just take our word for it – have a look at what Microsoft says in its support documentation, when it’s comparing some of the alternatives:

“the additional flexibility that Match and Index offer often enables you to significantly save time.  For example, you can store the result of an exact Match and reuse it in several Index statements”.

I’ve noticed that, when I stroll around the villages near where I live in the densely populated south east UK, there are a lot of different kinds of shiny 4WDs (presumably to help people cope with local speed bumps) parked on the gravelled driveways. 

On the occasions I’ve gone somewhere that is properly remote and rough, I notice the proportion of Toyota Landcruisers shoots up dramatically.  I am guessing there’s a reason for that.  When the risks of travelling remotely extend beyond just wanting to get comfortably over speed bumps, and when vehicle reliability is of critical importance (and could save your life), those in the know seem to opt for a Landcruiser. 

In this Excel analogy, I think Index (combined with Match) is the Toyota Landcruiser.  There’s a reason why those in the know stick with it.