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))
- The Table Array –> Data!A1:AX1000
- The Row No. of the ITEM –> MATCH(IF(XFB1,INDIRECT(A2&”_Year”),INDIRECT(B2&”_Year”)), Data_Row,0)
- The Column No. of the ITEM –> MATCH(C1,Data_Col,0)