INDEX Function

Returns an item from a collection based on provided row and column indexes.
Sample Usage
INDEX([Task Name]1:[Due Date]5, 1, 2)
Syntax
INDEX(
  • range
  • row_index
  • [
    column_index
    ]
)
  • range
    The group of cells that you want to evaluate.
  • row_index
    The row position (used in a one-dimensional collection such as a list) of the item to return.
  • column_index
    —[optional]
    The column position (used in a two-dimensional collection such as a table) of the item to return.
Usage Notes
  • The range can be a function that returns multiple values, for example: ANCESTORS or CHILDREN.
  • You can use the MATCH function inside of the INDEX function to return an item from a range based on the relative position of a value in a range. (See the example below.)

  • You can use the COLLECT function inside of the INDEX function to return an item from a range based on the collected values that meet the specified criteria. (See the example below.)

Examples

This example references the following sheet information:

 Clothing ItemTransaction TotalUnits SoldPrice Per UnitIn Stock?
1T-Shirt1,170.007815.00true
2Pants1,491.004235.50false
3Jacket812.00217200.00true

Given the table above, here are some examples of using INDEX in a sheet:

FormulaDescriptionResult
=INDEX([Clothing Item]:[Units Sold], 1, 3)Returns the value in the “Units Sold” column for row 1.78
=INDEX(COLLECT([Transaction Total]:[Transaction Total], [In Stock?]:[In Stock?], true), 1)Collects the values in the “Transaction Total” column for rows where the checkbox in the “In Stock?” column is checked (true), and returns the first entry in that collection.1,170.00
=INDEX([Price Per Unit]:[Price Per Unit], MATCH(“Pants”, [Clothing Item]:[Clothing Item], 0))Returns the value in the “Price Per Unit” column for the row that contains the value “Pants” in the “Clothing Item” column.35.50


 

Was this article helpful?
YesNo