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 Item Transaction Total Units Sold Price Per Unit In Stock?
1 T-Shirt 1,170.00 78 15.00 true
2 Pants 1,491.00 42 35.50 false
3 Jacket 812.00 217 200.00 true

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

Formula

Description

Result

=INDEX([Clothing Item]:[Units Sold], 1, 3)

Returns the value in row 1 of the Units Sold column 

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 check box 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(“Jacket”, [Clothing Item]:[Clothing Item], 0))

Returns the value in the Price Per Unit column for the row that contains the value Jacket in the Clothing Item column

$200

=INDEX([In Stock]:[In Stock], MATCH(“Jacket”, [Clothing Item]:[Clothing Item], 0))

Returns the value in the In Stock column for the row that contains the value Jacket in the Clothing Item column

true

Still need help? 

Use the Smartsheet Formula Template to view examples and practice working with functions in real-time.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community