- 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
- 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.)
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 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.