What Is MATCH Function In Google Sheets?
MATCH function in Google Sheets returns the position of a cell in a selected cell range or a dataset that matches the chosen lookup value.
The Google Sheets MATCH function acts as a support function in lookup scenarios where the lookup value can be anything, from a cell reference to a number, text, or logical value. For example, the data below consists of students and their scores. Let us fetch the relative position, i.e., row or column number of the lookup value ‘Chris’ from the student list, using the Google Sheets MATCH function.
Select cell E2, enter the formula =MATCH(D2,A2:A5,0) and press “Enter”, as shown below.
The function searches the position of the lookup value “Chris” in the range of cells A2:A5 and returns the result as 3, which is the actual position of “Chris” in the list.
Key Takeaways
- The MATCH function in Google Sheets helps users find the position of a specific value in the selected range of cells or table array. The function always returns a numeric value as a result.
- We can insert the search_key in multiple ways, namely, as a cell reference directly from the dataset, enter the cell value within double-quotes or insert it in a different cell and provide cell reference of the same.
- The counting of the position starts from the first selected cell of the cell range and not from the table’s header, unless the table header is included in the cell range.
- The MATCH+INDEX function can be used as an alternative for the VLOOKUP function andthe MATCH+FILTER helps us filter the matched data from the dataset.
- We see that the function is not case-sensitive. i.e., regardless of the font style or the letter case like Tubelight/tubelight/Tubelight, we get the precise results.
Syntax
The syntax of the MATCH formula in Google Sheets is,
The arguments of the MATCH formula in Google Sheets are,
- search_key: It is the value whose position we must locate in the range (second argument). It is a mandatory argument.
- range: It is either a range or a dataset to search for the lookup value. It is a mandatory argument.
- [search_type]: It is an optional argument which is the matching criteria defined for the lookup value. It can be:
- 1 = It will look for the largest value, either less than or equal to the search_key provided and return the approximate match. But the range must be in ascending order.
- 0 = It will look for and return the exact match to the search_key provided, irrespective of how the data is arranged or sorted.
- -1 = It will search for the smallest value, either greater than or equal to the search_key provided and return the approximate match. It requires the range to be arranged in descending order.
How To Use MATCH Function In Google Sheets?
We can use the MATCH Google Sheets Function in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon
Step 1: Choose an empty cell for the output 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “Lookup” option right arrow 🠖 select the “MATCH” function, as shown below.
Step 2: The “MATCH” formula appears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually
Step 1: Select an empty cell for the output.
Step 2: Type =MATCH( in the cell, as shown below. [Alternatively, type =M or =MAT and double-click the MATCH function from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some of the MATCH function in Google Sheets examples.
Example #1
Consider the example of some products and their units brought. We will use the Google Sheets MATCH function to find the position of a product according to the selected cell range.
The steps to find the position of “Tubelight” usingthe Match function are as follows:
Step 1: Select cell B9 and enter the formula =MATCH(A9 , as shown below.
[Note: We can insert the search_key in multiple ways, namely, as a cell reference directly from the dataset, enter the cell value within double-quotes or insert it in a different cell, as shown in this example in cell A9, and provide cell reference of the same.]
Step 2: Enter the cell range A2:A6, the [search_type] as 0 and close the brackets. The complete formula is =match(A9,A2:A6,0)
Step 3: Press “Enter” to get the result.
[Note: The counting of the position starts from the first selected cell of the cell range and not from the table’s header.]
The result of this function is 1, i.e., the position of “Tubelight” in the lookup array A2:A6.
Example #2 – MATCH Function with Date in Search Key.
We have the following employee details with their names, IDs, Date of Joining, Department and salary. Let us find the position of the search_key, where the search_key is a date value.
The steps to find the position of a Date usingthe Match function are as follows:
Step 1: Select cell I5 and enter the formula =MATCH(H5,D2:D11,0), as shown below.
Step 2: Press “Enter” to get the position of the selected date value, as shown below.
Even though we have multiple cells with the same date value, i.e. cells D4, D6 and D10, the formula checks for the first value and returns the position of the same.
Example #3 – MATCH Function with INDEX Function in Google Sheets.
For the same data of Example 2, we will only consider the employee names, ID and department. We will retrieve the needed information using the INDEX+MATCH functions which are a striking combination because it acts as an alternative for the VLOOKUP function.
The steps to retrieve the needed info using the INDEX and the MATCH functions are as follows:
Step 1: Select cell F6. Enter the formula =MATCH(E6,B2:B11,0), as shown below.
Step 2: Press “Enter” to get the MATCH results, as shown below.
Step 3: Select cell F9. Enter the formula =Index(C2:C11,MATCH(E9,B2:B11,0)), as shown below.
Step 4: Press “Enter” to get the INDEX+MATCH results, as shown below.
Now, we understand the result that the employee ID103 is in the 9th position of the selected cell range, and the corresponding employee name is “Kate”.
Step 5: For our reference let us check if the INDEX+MATCH is an alternative to the VLOOKUP function. Therefore, select cell F2, enter the formula =VLOOKUP(B10,B2:C11,2,0) and press “Enter”, as shown below.
We notice that the VLOOKUP retrieved the ID103’s employee name as “KATE”, same as the “INDEX+MATCH” result.
Example #4 – MATCH Function with FILTER Function in Google Sheets.
For the same data of Example 2, we will only consider the employee names, ID and department. We will retrieve the needed information using the INDEX+FILTER functions.
The steps to retrieve the needed info using the FILTER and the MATCH functions are as follows:
Step 1: Select cell F2. Enter the formula =MATCH(E2,A2:A11,0), as shown below.
Step 2: Press “Enter” to get the MATCH results, as shown below.
Step 3: To filter the data according to the MATCH results. Select cell F6 and enter the formula =Filter(A4:C4,MATCH(F5,A2:A11,0)), as shown below.
Step 4: Press “Enter” to get the FILTER+MATCH results, as shown below.
Now, we understand the result that the 3rd position data was matched as per the search_key and then using the FILTER function we retrieved or filtered the matched value row.
[Note: The filter function range will always be one row or one column.]
Important Things To Note
- The output of the MATCH formula will always be a numeric value.
- We get the #N/A error, if the entered search_key is not found in the selected range.
- We get the #REF error, if the cell reference provided is modified or deleted.
Frequently Asked Questions (FAQs)
The MATCH isn’t working because of the following reasons:
• The argument values or the cell reference are not found and we get the #Ref error.
• We have not provided the last optional argument. So, the output is incorrect or we get an error.
• The dataset or the lookup table array is updated, modified or deleted.
• As the search_key is not found in the selected cell range.
We often forget in which category a function falls, here, the “MATCH” function. Then, we can insert the function as follows:
Choose an empty cell 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “All” option right arrow 🠖 select the “MATCH” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the MATCH function in Google Sheets by following the path shown below.
• Choose an empty cell 🠖 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🠖 click the “All” option right arrow 🠖 select the “MATCH” function, as shown below.
Download Template
This article must be helpful to understand the MATCH function in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommeded Articles
Guide to What Is MATCH Function in Google Sheets. We learn to use it to find the position of a cell value with examples & working template. You can learn more from the following articles. –
Leave a Reply