MS Excel: INDEX Function (WS)

In Microsoft Excel, the INDEX function returns either the value or the reference to a value from a table or range. There are 2 syntaxes for the INDEX funciton.

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 optional. It is the column number in the array to use to return the value.

Applies To

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Worksheet Function Example

Let's take a look at an example to see how you would use the INDEX function in a worksheet:

Based on the Excel spreadsheet above, the INDEX function would return the following:

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 optional. It 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 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Worksheet Function Example

Let's take a look at an example to see how you would use this function in a worksheet:

Based on the Excel spreadsheet above, the INDEX function would return the following: