- search_value
- range
- [search_type]
- search_value—The value to search for.
- range—The cell range (lookup table) to be searched.
- search_type—[optional]The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).
Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns), then top to bottom (across rows). In a lookup table consisting of two columns, the cell in the top row of the leftmost column is the first position, 1.
For the optional search_type argument:
- 1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)
- 0: Finds the first exact match (the range may be unordered)
- -1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)
This example references the following sheet information:
| Row # | Clothing Item | Transaction Total | Units Sold | Price per Unit | Order Date |
|---|---|---|---|---|---|
| 1 | T-shirt | $1,170.00 | 78 | $15.00 | 02/12/19 |
| 2 | Pants | $1,491.00 | 42 | $35.50 | 02/15/19 |
| 3 | Jacket | $900.00 | 45 | $20.00 | 02/20/19 |
Given the table above, here are some examples of using INDEX in a sheet:
| Formula | Description | Result |
|---|---|---|
| =MATCH("Pants", [Clothing Item]:[Clothing Item], 0) | Returns the row position for Pants in the Clothing Item column |
2 |
|
=MATCH(42, [Units Sold]1:[Price Per Unit]3, 0) |
Returns the position of the numeric value 42 from the two-column table, where 78 would be 1st position and $20.00 is 6th position |
3 |
=MATCH(DATE(2019, 2, 12), [Order Date]:[Order Date]) |
Returns the position of the date February 12, 2019, using the DATE function, in the Order Date column |
1 |
=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 |
$20.00 |
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.
Learn more about formula combinations for cross sheet references.
Find examples of how other Smartsheet customers use this function, or ask about your specific use case in the Smartsheet online Community.