Back to the Basics – INDEX function

The INDEX function as this is one of the heavily used functions in Excel. Let’s dive straight into the syntax and examples.

= INDEX ( Table Range, Row Number, Column Number)
= INDEX (Data!A1:AX1000, 10, 3)
= INDEX (Data!A1:AX1000, MATCH(A2,Data!A1:A1000,0),MATCH(C1,Data!A1:AX1,0))

How about we visualize the function as follows –

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.

What if we needed to automate the referencing opposed to using hard-coded numbers for rows & columns? We include other functions as MATCH(), INDIRECT(), IF() etc to dynamically reference any cell within a given range. This is better explained below –

And what does dynamic referencing look like?

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

How to master the INDEX FORMULA – 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 dynamic references!

So, in a very complicated formula like the one below –

= 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)
Advertisements

One thought on “Back to the Basics – INDEX function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s