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 find 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:
| Clothing Item | Units Sold | Price Per Unit | Order Date | |
|---|---|---|---|---|
| 1 | T-Shirt | 78 | 15.00 | 02/12/19 |
| 2 | Pants | 42 | 35.50 | 02/15/19 |
| 3 | Jacket | 217 | 200.00 | 02/20/19 |
Given the table above, here are some examples of using MATCH in a sheet:
| Formula | Description | Result |
|---|---|---|
| =MATCH("Pants", [Clothing Item]:[Clothing Item], 0) | Returns the relative position (from left to right) of the string “Pants” in the unsorted “Clothing Item” column. Row 2 applies. | 2 |
| =MATCH(42, [Units Sold]1:[Price Per Unit]3, 0) | Returns the relative position (from left to right) of the number “42” in the unsorted range from row 1 of “Units Sold” to row 3 of “Price Per Unit.” Row 2 applies. | 3 |
| =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 string “Jacket” in the “Clothing Item” column. | 200.00 |