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