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 Item | Transaction Total | Units Sold | Price Per Unit | In Stock? | |
|---|---|---|---|---|---|
| 1 | T-Shirt | 1,170.00 | 78 | 15.00 | Brandfolder Image
|
| 2 | Pants | 1,491.00 | 42 | 35.50 | Brandfolder Image
|
| 3 | Jacket | 812.00 | 217 | 200.00 | Brandfolder Image
|
Given the table above, here are some examples of using INDEX in a sheet:
| Formula | Description | Result |
|---|---|---|
| =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
|
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.