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)**

Keila GrimlieLoving the info on this web site, you have done outstanding job on the content.