MATCH Function

Example 1: Basic in-sheet position finding

Let's say you have a column of Product Names, and you want to know which row number the item Jacket is on.

Formula: =MATCH("Jacket", [Product Name]:[Product Name], 0)

Result: 3

The formula returns 3 because Jacket is in the third position within the range.

Example 2: Reference the current row dynamically

In Example 1, the formula searches for a hardcoded value, which means that it always returns the position of "Jacket", no matter where you use the formula. But what if you want MATCH to automatically find the position of whatever product is listed in each row?

Replace the hardcoded value with [Product Name]@row. This tells MATCH to use the value in the current row as the search value, making the formula reusable across every row in your sheet.

Add a Position column to your sheet and enter this formula:

=MATCH([Product Name]@row, [Product Name]:[Product Name], 0)

Each row now returns its own position automatically. If the rows are reordered, new rows are added, or existing rows deleted, the Position value updates to match the new position within the sheet. 

This is especially useful when you use MATCH as part of an INDEX(MATCH) formula, and instead of looking up one fixed item, your formula adapts to each row on its own.

Example 3: (search_type 1) and (search_type -1)

Examples A and B use the following reference table. To apply it in your formulas, add a table to a separate sheet and create cross-sheet references.

Alphabetical group assignments

Leave Row 1 of the search range blank, use A (for text) or 0 (for numbers) to avoid getting a #NO MATCH error message if your search value is before your first “cuttoff” value. Alternatively, use IFERROR to define the value you want to see for items that happen before the first cutoff. 

Example A: Find an approximate match in ascending order (search_type 1)

Use search_type 1 when your data is sorted A–Z or smallest to largest, and you want to find the closest value without going over, like assigning values to a bracket or category based on where they fall in a range.

Scenario: You're managing event registrations and need to assign each attendee to a group based on their last name. Instead of a long nested IF formula that breaks every time the cutoffs change, you maintain a small reference table and let MATCH do the work.

When MATCH uses search_type 1, it finds the position of the last cutoff value that doesn't come after the attendee's last name. That position is the group number.

="Group " + MATCH([Last Name]@row, {Cutoff}, 1)

For "Johnson," MATCH scans the Cutoff column in ascending order and finds that "Johnson" falls after "Jo" (Group 5's cutoff) but before "Ke" (Group 6's cutoff). It returns position 5, and the formula outputs "Group 5."

Using MATCH instead of a nested IF works best because when cutoffs change, for example, if the groups become unbalanced, you update the reference table but don't need to change the formula. You can add a new group, adjust a boundary, or rename a group, and the formula stays the same, simply using the new reference points.

For this to work correctly, the Cutoff column in your reference table must be sorted in ascending (A–Z) order, and have a blank row as the first range value, or an IFERROR to catch items before the first cutoff

Example B: Find an approximate match in descending order (search_type -1)

Example B uses the following reference table. To apply it in your formulas, add a table to a separate sheet and create cross-sheet references.

Newsletter schedule

Use search_type -1 when your data is sorted Z–A or largest to smallest, and you want to find the closest value at or above the search value, like matching a date to the next upcoming deadline.

Scenario: Your team submits content requests with a Requested Date (see table below). You need each row to automatically show which newsletter edition that content belongs to, based on the submission cutoff dates (as shown in the table above). The newsletter schedule is sorted in descending order (most recent first).

MATCH scans the Submission Cutoff Date column from top to bottom and finds the first cutoff date that's greater than or equal to the requested date. That's the edition the content belongs to.

=INDEX({Newsletter}, MATCH([Requested Date]@row, {Submission Cutoff Date}, -1))

For a requested date of 09/20/27, MATCH scans the Submission Cutoff Date column (sorted descending) and finds 09/25/27 as the first cutoff date that's greater than or equal to 09/20/27. That's position 1 in the table, which corresponds to Q4 2027.

INDEX then uses that position to return the newsletter name from the Newsletter column.

For this MATCH example to work correctly, the Submission Cutoff Date column in your reference table must be sorted in descending (Z–A or newest to oldest) order.

Example of using MATCH in a sheet

This example references the following sheet information:

Based on the table above, here are some examples of using MATCH in a sheet:

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.

Ask the Community