MATCH Function | Smartsheet Learning Center

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:

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	$812.00	217	$200.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 $200.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.	$200.00