Returns an item from a collection 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 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
Sample usage
INDEX([Task Name]1:[Due Date]5, 1, 2)
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?
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.