The Excel Index function returns a reference to a cell that lies in a specified row and column of a range of cells.
There are two formats of the function, which are the Array Format (which is the most basic format), and the Range Format of the function. These are described separately below.
The Array format of the Index function is used when you want to look up a reference to a cell within a single range.
The syntax of the function is:
Where the arguments are as follows:
array    The specified array or range of cells. 
row_num    Denotes the row number of the specified array. If set to zero or blank, this defaults to all rows in the supplied array. 
[col_num]    Denotes the column number of the specified array. If set to zero or blank, this defaults to all columns in the supplied array. 
Note that the row_num and the [col_num] arguments cannot both be zero or blank.
In the following example, the Index function returns a reference to row 5 of the range C1:C5, which is cell C5. This has the value 8.
Formula  Result 
In the following example, the Index function returns a reference to row 5 and column 2 of the range C1:D5, which is cell D5. This has the value 3.
Formula  Result 
In the following example, as the supplied [col_num] is set to 0, the Index function returns a reference to all of row 5 of the supplied range, which is the range C5:D5.
In the example, the result of the Index function is then passed into the Sum function and so the formula returns the sum of the range C5:D5, which is 11.
Formula  Result 
In example 3 above, the Index function returned the range C5:D5. If we had wanted to enter the values of each of the cells, C5 and D5 into our spreadsheet, the Index function would need to be entered as an array formula. This is illustrated in the example below.
Array Formulas:
To input an array formula, you need to first highlight the range of cells that will contain the function result. Type your function into the first cell of the range, and press CTRLSHIFTEnter.In the following example, the supplied row_num is 0, and so the Index function returns a reference to all of column 2 of the supplied range, C1:D5. I.e. the function returns a reference to the range D1:D5.
In this case, the function is input into cells A1:A5, as an Array Formula. This can be seen by the curly brackets surrounding the function in the formula bar of the Result spreadsheet.
Formula  Result 
The Range format of the Index function can be used to extract references from ranges that are made up of more than one area.
The syntax of the function is:
Where the arguments are as follows:
range    The specified array or range of cells. Note: If multiple areas are input directly into the function, the individual areas should be separated by commas and surrounded by brackets  ie. ( A1:B2, C3:D4, etc). 
row_num    Denotes the row number of the specified area. If set to zero or blank, this defaults to all rows of the specified area within the supplied range. 
[col_num]    Denotes the column number of the specified area. If set to zero or blank, this defaults to all columns of the specified area within the supplied range. 
[area_num]    If the initial specified range is made up of more than one area, the [area_num] argument specifies the number of the area to be used. (Note that the areas are numbered by the order they are specified). If the [area_num] argument is omitted, it defaults to the value 1 (i.e. the reference is taken from the first area in the supplied range. 
Note that either (but not both) of the row_num or the [col_num] arguments may be zero or blank. If they are both zero or blank, the Index function will return an error.
In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area in the supplied range. This is cell D4, which evaluates to the value 5.
Formula  Result 
In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area in the supplied range. This is cell B12, which evaluates to the value 7.
Formula  Result 
zero In the following example, as the supplied [col_num] is blank, the Index function returns a reference to all of row 3 of the 3rd area of the supplied range. This is the range B12:D12.
The result of the Index function is then passed into the SUM function, which returns the value 10.
Formula  Result 
Further details and examples of the Excel Index function are provided on the Microsoft Office website.
If you get an error from the Excel Index function this is likely to be one of the following:
#REF!    Occurs if either:

#VALUE!    Occurs if any of the supplied row_num, [col_num] or [area_num] arguments are not numeric values. 