-
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 optionalsearch_typeargument:
- 1:(The default value) Finds the largest value less than or equal tosearch_value(requires that the range be sorted in ascending order)
- 0:发现第一个精确匹配(可能unor范围dered)
- -1:Finds the smallest value greater than or equal tosearch_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 forPantsin theClothing Itemcolumn |
2 |
=MATCH(42, [Units Sold]1:[Price Per Unit]3, 0) |
Returns the position of the numeric value42from the two-column table, where78would be 1st position and$20.00is 6th position |
3 |
=MATCH(DATE(2019, 2, 12), [Order Date]:[Order Date]) |
Returns the position of the dateFebruary 12, 2019, using the DATE function, in theOrder Datecolumn |
1 |
=INDEX([Price Per Unit]:[Price Per Unit], MATCH("Jacket", [Clothing Item]:[Clothing Item], 0)) |
Returns the value in thePrice per Unitcolumn for the row that contains the valueJacketin theClothing Itemcolumn |
$20.00 |
Still need help?
Use theFormula Handbook templateto 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.