tech on the net

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

Type of Function

Worksheet Function Example

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

Microsoft Excel

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

=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 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

Type of Function

Worksheet Function Example

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

Microsoft Excel

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

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