Excel: Index Function
In Excel, the Index function returns either the value or the reference to a value from a table or range.
Syntax #1 - Returning a value
The first implementation of the Index function returns the value from a table or range. The syntax for the Index function is:
Index( array, row_number, column_number )
array is a range of cells or table.
row_number is the row number in the array to use to return the value.
column_number is the column number in the array to use to return the value.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at an example:
Based on the Excel spreadsheet above:
=Index(A1:D5, 2, 3) would return $3.50 =Index(A1:D5, 4, 1) would return 10569 =Index(A1:D5, 5, 2) would return 12
Syntax #2 - Returning a Reference to a Value
The second implementation of the Index function returns the reference to a value from a table or range. The syntax for the Index function is:
Index( reference, row_number, column_number, area_number )
reference is a reference to a range or group of ranges.
row_number is the row number in the range to use to return the reference.
column_number is the column number in the range to use to return the reference.
area_number is optional. It is the range to use from the reference parameter. If this parameter is omitted, then the Index function will return its results for the first range listed in the reference parameter.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at an example:
Based on the Excel spreadsheet above:
=Index(A1:D5, 2, 3) would return return the reference to cell C2 (which contains the value of $3.50) =Index((A1:B5, C1:D5), 3, 2) would return the reference to cell B3 (which contains the value of 4) =Index((A1:B5, C1:D5), 3, 2, 1) would return the reference to cell B3 (which contains the value of 4) =Index((A1:B5, C1:D5), 3, 2, 2) would return the reference to cell D3 (which contains the value of $20.00)