When working with large databases in Google Sheets, you often need to know the exact position of a specific item within a row or column. For example, if you have a list of 500 employee names, you might need a formula to tell you that “Sarah Jenkins” is located in row 245.
To find the relative position of an item, you use the MATCH function. While MATCH is rarely used completely on its own, it is a foundational formula that is frequently combined with the INDEX function to create powerful, flexible search queries that outperform the standard VLOOKUP.
In this guide, you will learn the exact syntax of the MATCH function and how to use it to locate data in Google Sheets.
The Basic MATCH Syntax
The MATCH function requires three arguments to work correctly:
=MATCH(search_key, range, [search_type])- search_key: The exact text, number, or cell reference you are looking for.
- range: The single row or single column you want Google Sheets to search through.
- search_type: A number (0, 1, or -1) dictating how strictly Google Sheets should match your search key.
How to Find an Exact Match
The most common scenario is searching for an exact match. Imagine you have a list of product ID codes in column A, from cell A2 down to A100. You want to find out which row contains the product ID “XYZ-99”.
Click into an empty cell and type the following formula:
=MATCH("XYZ-99", A2:A100, 0)Here is what this formula does:
- It searches for the exact text string “XYZ-99”.
- It scans through the range A2 to A100.
- The
0at the end is the most critical part. Setting the search type to 0 forces Google Sheets to find an Exact Match. If the data is not sorted alphabetically, you must use 0, otherwise, the formula will return wildly incorrect results.
If “XYZ-99” is the 10th item in your list, the formula will output the number 10. Note that it outputs the relative position within the range, not the absolute row number of the spreadsheet.
How to Find Approximate Matches (1 or -1)
In rare financial or statistical scenarios, you might need to find the closest approximate match rather than an exact string.
Using Search Type 1 (Less Than or Equal To)
If you set the search type to 1, the MATCH function assumes your range is sorted in ascending order (A-Z, or 1-100). It will search for your key. If it cannot find an exact match, it will return the position of the largest value that is less than or equal to your search key.
=MATCH(45, B2:B10, 1)If the number 45 does not exist in the list, but 40 and 50 do, it will return the position of 40.
Using Search Type -1 (Greater Than or Equal To)
If you set the search type to -1, the function assumes your range is sorted in descending order (Z-A, or 100-1). If it cannot find an exact match, it will return the position of the smallest value that is greater than or equal to your search key.
Using MATCH with Wildcards
Just like the COUNTIF function, MATCH supports wildcards for partial text searches, but only when the search type is set to 0 (Exact Match).
If you want to find the position of the first employee whose last name is “Smith” in column A, you can use the asterisk (*) wildcard:
=MATCH("*Smith", A2:A100, 0)This tells the function to look for any cell that ends with “Smith”, regardless of what their first name is. It will output the position of the first matching cell it encounters.
By understanding how to extract the exact coordinate position of your data using the MATCH function, you lay the groundwork for building advanced, dynamic INDEX-MATCH lookups that can navigate the most complex spreadsheets.