|
|
The Excel INDEX Function
The Array Format of the Excel INDEX FunctionThe Array format of the Index function is the most basic form of the function, and is used when you want to look up a reference to a cell within a single range. The syntax of the function is :
INDEX( array, row_num, col_num )
Where the arguments are as follows :
The function is best explained through the following examples: Example 1In the following example, the Index function returns a reference to row 5 of the supplied range, which is cell C5. This evaluates to the value 8
Example 2In the following example, the Index function returns a reference to row 5 and column 2 of the supplied range, which is cell D5. This evaluates to the value 3
Example 3In 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. The sum of this range evaluates to the value 11
Array Formulas:
To input an array formula, you need to first highlight the range of cells for the function
result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.
Go to the Excel Array Formulas page for more details. Example 4In the following example, the supplied row_num is set to 0, and so the Index function returns a reference to all of column 2 of the supplied range. 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. Therefore, the values of the five cells returned by the Index function are displayed in cells A1:A5.
The Range Format of the Excel INDEX FunctionThe Range format of the Index function can be used to extract references from ranges that are made up of more than one area. The format of the function is :
INDEX( range, row_num, col_num, area_num )
Where the arguments are as follows :
This form of the Index function is illustrated via the following examples: Example 1In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area (ie. C1:D5) in the supplied range. This is cell D4. This evaluates to the value 5
Example 2In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area (ie. B10:D12) in the supplied range. This is cell B12, which evaluates to the value 7
Example 3In 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 (ie. B10:D12) of the supplied range. This is the range B12. The sum of this range evaluates to the value 10.
Further information and examples of the Excel Index function can be found on the Microsoft Office website. Index Function ErrorsIf you get an error from the Excel Index function this is likely to be one of the following: Common Errors
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Copyright © 2008-2011 ExcelFunctions.net |
