Despite unlimited online help and material, I find people are always confused when it comes to INDEX. Actually, for that matter, any function in Excel I should say, till they get very comfortable.

The problem occurs particularly when multiple nested functions are involved, making it quite cumbersome and tedious to debug/explore. Although, with what could be a very meticulous approach of indenting and use of line returns to mark end of each separate level of formula we could make the whole process easier, a stronger understanding of the process or function in this case, could help you a longer way! And that’s what I intend to do with the ‘Back to the Basics’ series…

Let’s take up the INDEX function as this is one of the ‘most sought after formula’ in Excel, yet quite a pain if you did not know how this worked. Let’s quickly take an example from below -

= INDEX (Data!A1:AX1000, MATCH(IF(XFB1,INDIRECT(A2&”_Year”),INDIRECT(B2&”_Year”)), Data_Row,0),MATCH(C1,Data_Col,0))

A formula like above, and the newer folks are quickly put off – even before they started to try! Once you get to know what this is all about, you’ll jump at the opportunity to look at a function like this! Trust me…

For starters, I prefer to visualize my excel operations in a very pictorial way and that’s MY way of doing it – but the essence still remains, you have to visualize the ‘BASICS’! So, let’s get started then…

First off, INDEX function is used to pick a value from a table/matrix/2D array – call it anyway you please. The syntax goes like this -

= INDEX ( Table Range, Row Number, Column Number)

Let’s take an example….

And if I wanted to pick the numbers in the columns C & D one by one, how would I do it? I would like to use the INDEX function for this very purpose, in this way -

= INDEX(A2:D7, 2, 3)                 –>  would give B’s Orders total of 345

= INDEX(A2:D7, 4, 4)                 –>  would give D’s Sales total of 20.6

Or this could be done another way…

= INDEX(C2:C7, 2, 1)                 –>  would give B’s Orders total of 345

= INDEX(D2:D7, 4, 1)                 –>  would give D’s Sales total of 20.6

Either way, we get what we wanted, the Columns C & D referenced.

In both the cases above, what mattered most is – the relative referencing of the Row & Column No. to the Table that has been defined.

Now, if it’s so simple, why all the complications in the formula? Well, in the formulae above, the Row & Column No. has been hard coded – i.e. it’s is fixed and doesn’t change. What if we needed something more automated, something more dynamic? That’s when we use different methods to arrive at the Row & Column No. – INDIRECT, MATCH etc.. Take a

look below..

If we wanted to reference the data of any of the above queries (Yes, that’s the technical term for fetching some data given some criteria), the formulae to the right is how we would do it – but would that help? If I changed any of the criteria, the formula wouldn’t hold good any longer! Why? Since the row & column no.s are hard coded! So, if I wanted to check for orders for A, I’ll have to manually edit the formula to =INDEX(A2:D7,2,3).

This is where dynamic referencing makes all the difference! Take a look below -

Agreed this look more complicated, but this does all the work you want! All it does is, in the places where we had the Row & Column No.s, we plug in a formula that will get us that by looking up our criteria! Brilliant, ain’t it?!

So, above, even if I were to change my criteria to anything else, like A’s Orders or E’s Sales etc, the formula would automatically reference the correct data!

And that’s all there is to the INDEX formula!!!

Do me this favour – Next time, you encounter the INDEX, break it down into the following 3 components:

  1. The Table Array
  2. The Row No. of the ITEM
  3. The Column No. of the ITEM

And then, go about debugging/creating your dynamic references! And you’ll be home in no time!!!

So, taking a look at what we had started with -

= INDEX (Data!A1:AX1000, MATCH(IF(XFB1,INDIRECT(A2&”_Year”),INDIRECT(B2&”_Year”)), Data_Row,0),

MATCH(C1,Data_Col,0))

  1. The Table Array –> Data!A1:AX1000
  2. The Row No. of the ITEM –> MATCH(IF(XFB1,INDIRECT(A2&”_Year”),INDIRECT(B2&”_Year”)), Data_Row,0)
  3. The Column No. of the ITEM –> MATCH(C1,Data_Col,0)

So, go ahead and crack down every little piece of formula you have now!

Advertisement