# MATCH Function

Returns the relative position of a value in a range (lookup table). The first position is 1.
Sample Usage
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

In Stock

1

T-Shirt

\$1,170.00

78

\$15.00

true

2

Pants

\$1,491.00

42

\$35.50

false

3

Jacket

\$812.00

217

\$200.00

true

Given the table above, here are some examples of using INDEX in a sheet:

Formula

Description

Result

=INDEX([Clothing Item]:[Units Sold], 1, 3)

Returns the value in the “Units Sold” column for row 1.

78

=INDEX(COLLECT([Transaction Total]:[Transaction Total], [In Stock?]:[In Stock?], true), 1)

Collects the values in the “Transaction Total” column for rows where the checkbox in the “In Stock?” column is checked (true), and returns the first entry in that collection.

\$1,170.00

=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

=INDEX([In Stock]:[In Stock], MATCH(“Jacket”, [Clothing Item]:[Clothing Item], 0))

Returns the value in the “In Stock” column for the row that contains the value “Jacket” in the “Clothing Item” column.

true