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