How to Use Google Sheets MATCH

The Google Sheets MATCH function finds a specific item you want to look up and tells you the position of that item in the range. This function allows you to create your own search engine of sorts, with the power to find the relative position of cells containing a specified value (either numbers, text, or dates) in the selected range of cells.

After this tutorial, you’ll learn how to use the MATCH function, with steps you can try at your own pace.

How to use the Google Sheets MATCH function

Follow these three steps to use the MATCH function:

1. Select a cell range in Google Sheets.

Open Google Sheets in your web browser and select the cells you want to include in your match. When selecting your range, consider that the MATCH function only allows a range of one single row or column, not multiple or mixed. Selecting multiple rows or columns as your range will prompt an error.

2. Use the MATCH function to locate the item within your cell range.

To choose the range that contains what you’re looking for, go to the toolbar menu and select Insert > Function > Lookup > MATCH.

As a shortcut, you can select an empty cell in your row or column and type =match( ).

3. Enter the MATCH function parameters for your search.

The Google Sheets MATCH function has a built-in syntax that calls for three parameters: a search key, a range, and the search type.

=MATCH(search_key, range, [search_type])

search_key: Enter the numeric or text value you want to search for in your cell range. Use quotation marks (“ “) around this parameter if it’s an text value (“apple”); this isn’t necessary for numerical values.

If you want to search for a date, use the “DATE (year, month, day)” format in the search key. For example, =MATCH( DATE (2023,12,31),...”

range: Enter the cell range (within a single column or row) where you want to search for the value—the start and end of the range will be separated by a colon (:).

search_type: This parameter specifies how the function should perform the match. You can enter a 0 to find an exact match, 1 for an approximate match less than or equal to the search_key (when range is in ascending order), or -1 for an approximate match greater than or equal to the search_key (when range is in descending order). If you don’t enter a search type, it will default to 1.

For example, if you want to find the exact match of the word ‘Barbie’ anywhere between B2 and B16, your function would read as follows: =MATCH("Barbie", B2:B16, 0)

Once you fill out these parameters, press the Enter key to get your result. The function will return a number representing the position of your value relative to the beginning of the specified cell range. In our example, “Barbie” is located in the second position in the cell range we entered.

How to troubleshoot Google Sheet MATCH function

You might encounter an error when using the Google Sheets MATCH function for several reasons. If you’re sure the data is in the spreadsheet but can’t locate it when using the match function, check the cell for any unexpected characters—even a minor typo could produce an error.

Other things that can lead to an error include not typing in the exact search term you’re looking for, leaving the range blank, adding an incorrect search type, or choosing the wrong range.

The difference between Google Sheets MATCH and VLOOKUP

The main difference between the functions MATCH and VLOOKUP is what they return and how they handle columns. MATCH will provide the position of the specified value in only a single row or column. However, VLOOKUP provides the coordinating value to your search term across another row or column. In our example above, MATCH returns “2,” the exact position of the word “Barbie” in the specified column. With VLOOKUP, you can find the exact box office earnings for the search_key “Barbie.” VLOOKUP will search the first column of a given range and return the value of a specified cell in the row found.

Both functions have their advantages, but the choice between them should be guided by the specific information you need and the execution speed you require.

Learn more about Google Sheets MATCH on Coursera.

If you are interested in gaining the analytical skills to present your data skillfully, you can enroll in the Google Data Analytics Professional Certificate on Coursera. You will learn in-demand skills, including data cleansing, problem-solving, and organizing your data for analysis.