INDEX

Syntax:

array form: INDEX ( array , [ row-number ] [ , [ column-number ] ] )

reference form: INDEX ( reference [ , [ row-number ] [ , [ column-number ][ , [ area-number ] ] ] ] )

Description: Locates a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array form and the reference form.

Arguments:

Name

Type

Description

array

array, reference

Table or range to be searched. If array contains only one row, the corresponding row-number argument is optional. If array contains only one column, the corresponding column-number argument is optional.

reference

reference

A reference to one or more cell ranges. If each area (§3.17.2.3) in reference contains only one row, row-number is optional. If each area contains only one column, column-number is optional.

row-number

number

row-number indicates the row in array (or reference) from which to return a value (or reference). If row-number is omitted, column-number shall be present. column-number indicates the column in array (or reference) from which to return a value (or reference). If column-number is omitted, row-number shall be present. If both the row-number and column-number arguments are used, INDEX returns the value (or reference) in the cell at the intersection of row-number and column-number. If array has more than one row and more than one column, and only row-number or column-number is used, INDEX returns an array of the entire row or column in array. If row-number or column-number, but not both, is 0, INDEX returns the array of values for the entire column or row, respectively. In the reference form, if row-number and column-number are both omitted, INDEX returns the area in reference specified by area-number.

column-number

number

area-number

number
Indicates a range in reference from which to return the intersection of row-number and column-number. The first area selected or entered is numbered 1, the second 2, and so on. If area-number is omitted, 1 is assumed.
example: : If reference describes the cells (A1:B4, D1:E4, G1:H4), then area-number 1 is the range A1:B4, area-number 2 is the range D1:E4, and area-number 3 is the range G1:H4.

Return Type and Value: various – For the array form, returns a single value, a whole row, or a whole column from a table or an array, depending on the presence and values of the row and column number indexes.

For the reference form, returns a single reference, a whole row, or a whole column from a reference, depending on the presence and values of the row and column number indexes, and the area number.

However, for the array form

  • row-number is outside the bounds of array, #REF! is returned.

  • column-number is outside the bounds of array, #REF! is returned.

For the reference form

  • row-number is outside the bounds of reference, #REF! is returned.

  • column-number is outside the bounds of reference, #REF! is returned.

  • area-number is outside the bounds of reference, #REF! is returned.

example:

Given the following data:

A

B

C

1

Fruit

Price

Count

2

Apples

0.69

40

3

Bananas

0.34

38

4

Lemons

0.55

15

5

0.25

25

6

Pears

0.59

40

7

Almonds

2.8

10

INDEX(A2:C7,2,3) results in 38INDEX((A2:C4,A6:C7),2,2,2) results in 2.8INDEX((A2:C4,A6:C7),2,2,1) results in 0.34]