Returns the relative position of a value in a range (lookup table). The first position is 1.
Sample Usage
MATCH("Task A", [Task Name]1:Done5)
Syntax
MATCH(
-
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).
Usage Notes
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)
Examples
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 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.