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 COLLECT function inside of the INDEX function to return an item from a range based on the collected values that meet the specified criteria. For example, the formula:

    =INDEX(COLLECT([Task Name]:[Task Name], [Assigned To]:[Assigned To], "John", [Not Complete]:[Not Complete], 1), 1)

    will return the value Task B. The formula is written to return the task that is assigned to John and is marked as not complete. (See the image below for an example of this formula in action.)

    index function with collect function

Examples

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.

This example references the following sheet information:

 Inventory ItemAmount OrderedCost per Item
1Necklace1$200
2Earrings4$75
3Ring2$160

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

FormulaDescriptionResult
=INDEX([Inventory Item]1:[Cost per Item]3,MATCH(Item1,[Inventory Item]1:[Cost per Item]3,0), 3) * [Amount Ordered]1Return the cost of the Necklace and multiply it by the amount ordered$200
=INDEX([Inventory Item]1:[Cost per Item]3, MATCH(Item2, [Inventory Item]1:[Cost per Item]3, 0), 3) * [Amount Ordered]2Return the cost of the Earrings and multiply it by the amount ordered$300
=INDEX([Inventory Item]1:[Cost per Item]3, MATCH(Item3, [Inventory Item]1:[Cost per Item]3, 0), 3) * [Amount Ordered]3Return the cost of the Ring and multiply it by the amount ordered$320

The MATCH function determines the row position of the inventory item to return. (The 0 at the end of the MATCH portion of the formula ensures that an exact match is found.) The INDEX function then returns the corresponding cost from the range based on provided row and column indexes. The formula multiplies that cost by the Amount Ordered to determine the total order cost.

Was this article helpful?
YesNo