INDEX Function

Returns a value from a range based on provided row and column indexes

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 selected range with one column, such as a list) of the item to return
  • column_index—[optional]
    The column position (used in a selected range with two or more columns, such as a table) of the item to return

Sample usage

INDEX([Task Name]:[Task Name], 5
INDEX(CHILDREN([Task Name]@row), 1)

Usage notes

  • The range can be a function that returns multiple values, for example: ANCESTORS, CHILDREN, or COLLECT.
  • You can use the MATCH function as the row_index value for INDEX to return an item from a range based on the relative position of a value in another range. (See the example below.)
  • You can use the COLLECT function as the range value for INDEX to return an item from a list of 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.00
Brandfolder Image
Checked checkbox.
2Pants1,491.004235.50
Brandfolder Image
Unchecked checkbox.
3Jacket812.00217200.00
Brandfolder Image
Checked checkbox.

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

FormulaDescriptionResult
=INDEX([Clothing Item]1:[Units Sold]3, 1, 3)Returns the value in row 1 of the Units Sold column 78
=INDEX(COLLECT([Transaction Total]1:[Transaction Total]3, [In Stock?]1:[In Stock?]3, 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]1:[Price Per Unit]3, MATCH(“Jacket”, [Clothing Item]1:[Clothing Item]3, 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.

Since the In Stock? Column is a checkbox. The column where you type this formula must be a checkbox type, too. 

Brandfolder Image
Checked checkbox.

Ensure the type of data you will get from the INDEX formula matches the column type where you are using the formula. This includes formatting such as currency or percentages if you wish to see the data displayed in that format. 


Still need help?

If you have an Enterprise plan, you can use AI to help write and troubleshoot formulas.

Use the Formula Handbook template to find more support resources and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

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