Sintassi
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]Defines if exact matches are required (0), or if approximate matches are allowed (1, -1). Approximate matches can only be determined when the data is sorted in ascending (1) or descending (-1) order.
Utilizzo esempio
MATCH("Task A", [Task Name]:[Task Name], 0)
Utilizzo note
Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns), then top to bottom (across rows) within the selected range.
- In a lookup table consisting of two columns, the cell in the top row of the leftmost column is the first position, 1, regardless of its position within the sheet.
- When working with text (string) data, the function considers letters later in the alphabet as greater than the search value and letters earlier in the alphabet as smaller.
- When the range is unsorted and search_type is undefined, the MATCH function may fail to find the expected value when a larger value is located early in the list.
For example, if you have a column listing various products, the MATCH function can tell you if Pants is the first, fifth, or tenth product on that list. It returns a number representing that item's relative place, with the very first item in the list always being position 1. This is particularly useful when you need to combine it with other functions, like INDEX, to retrieve related information.
For the optional search_type argument:
- 1: (The default value) Reviews the range until it finds an exact match, or a value greater than the search value. If it finds a value beyond the search value, the result is the previous position of that larger value.
- 0: Finds the first exact match (No sort order is required)
- -1: Reviews the range until it finds an exact match, or a value smaller than the search value. If it finds a value before the search value, the result is the previous position of that smaller value.
Esempi
This example references the following sheet information:
| Row # | Clothing Item | Transaction Total | Units Sold | Price per Unit | Order Date |
|---|---|---|---|---|---|
| 1 | T-shirt | $1,950.00 | 78 | $15.00 | 02/12/25 |
| 2 | Pants | $1,491.00 | 42 | $35.50 | 02/15/25 |
| 3 | Jacket | $900.00 | 45 | $20.00 | 02/20/25 |
Based on 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 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(2025, 2, 14), [Order Date]:[Order Date], 1) |
Order Date column is in ascending order. | 1 |
| =MATCH(1500, [Transaction Total]:[Transaction Total], -1) | Returns the position of the closest number equal to or greater than 1500 in the Transaction Total column. The Transaction Total column is in descending order. | 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. | 2/20/2025 |
Still need help?
Use the Formula Handbook template to 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.
Learn more about formula combinations for cross sheet references.
Find examples of how other Smartsheet customers use this function, or ask about your specific use case in the Smartsheet online Community.