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, |
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.
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 38
INDEX((A2:C4,A6:C7),2,2,2)
results in 2.8
INDEX((A2:C4,A6:C7),2,2,1)
results in 0.34
]