INDEX
The INDEX function returns the contents of a cell from an area based on row and column numbers.
It is a powerful tool for working with spreadsheets and arrays of data.
Syntax
INDEX(area, row_num, [column_num], [range_num])
| Argument | Description | Permitted values |
|---|---|---|
| area | Array, range, or list of ranges from which you want to select an item | Any range or array of data |
| row_num | Row number in the specified range or array | Integer or a reference to a cell containing a number |
| [column_num] | (optional) Column number in the specified range or array. By default, 1 | Integer or a reference to a cell containing a number |
| [range_num] | (optional) Number of the range in which you want to select an item. By default, 1 | Integer greater than 0 or a reference to a cell containing a number |
Examples of use
Standard usage
=INDEX(A1:C3, 2, 3)
This formula will return the value from the second row and third column of the range A1:C3.
Usage with a single column
=INDEX(A1:A5, 3)
The formula will return the value from the third row of column A (cell A3).
Combining with other functions
=INDEX(A1:C3, MATCH("Item", A1:A3, 0), 2)
Here, the MATCH function searches for the row containing "Item", and INDEX returns the corresponding value from the second column.
Reference to the entire column
=SUM(INDEX(A1:C5, 0, 3))
In this example, the INDEX function with the row_num argument equal to 0 returns a reference to the entire third column (C1:C5), and the SUM function then adds up all the values in that column.
Notes
- The INDEX function can be used to retrieve data from large spreadsheets and can be combined with other functions, such as MATCH, to create powerful formulas.
- It is important to ensure that the specified row and column numbers are within the range, otherwise the function will return the #REF! error.
- When you specify 0 in the row_num argument, the function returns the entire column of the specified range. Similarly, if 0 is specified for column_num, the entire row is returned. This only works in array formulas or in combination with some other functions (such as SUM).