This guide covers everything you need to know about the Google Sheets MATCH function, including its definition, syntax, use cases, and how to use it.
What is the MATCH Function? How Does It Work?
The MATCH function in Google Sheets returns the relative position of an item in a range that matches a specified value. It’s an essential function for those who often deal with large datasets and need to locate specific information within them.
This function works by searching for a specified value, known as the search key, within an array or range of cells. This range must be one-dimensional, meaning it can only have a height or width greater than one, not both. The MATCH function will return an error if a two-dimensional range is used.
You can customize how the MATCH function searches through your data by adjusting the search type. There are three options available. By default, the function assumes that the range is sorted in ascending order and returns the largest value less than or equal to the search_key. If you prefer an exact match, you can set the search type to 0, but this requires the range to be unsorted. Alternatively, if your range is sorted in descending order, set the search type to -1, and the function will return the smallest value greater than or equal to the search_key.
It’s important to note that the MATCH function gives you the position of the matched value in the array or range, not the value itself. So, if you are looking for the actual value or another value corresponding to the row or column of the matched item, use INDEX, HLOOKUP, or VLOOKUP instead.
MATCH Syntax
The syntax and arguments for the function are as follows:
MATCH(search_key, range, [search_type])
Here’s a detailed breakdown of the arguments:
- search_key: This is the value that you’re looking for. It could be a number, a text string, or a cell reference. For instance, you could use 42, “Cats”, or I24 as your search_key.
- range: This refers to the one-dimensional array or range of cells you want to search. It’s important to note that if you use a range with a height and width greater than 1, MATCH will return #N/A!.
- search_type: This is an optional argument, and its default value is 1. This argument dictates how MATCH will conduct the search.
- If you use 1 (the default), MATCH will assume that the range is sorted in ascending order and will return the largest value that is less than or equal to the search_key.
- If you use 0, this indicates that you want an exact match. This is necessary when the range is not sorted.
- If you use -1, MATCH will assume that the range is sorted in descending order and will return the smallest value greater than or equal to the search_key.
Here are some important usage notes related to the syntax and arguments:
- The search_key argument is not case-sensitive. This means that if you search for a text string, MATCH will not differentiate between upper- and lower-case letters.
- If the search_type argument is either 1 or -1 and no exact match is found, the function will return the position of the next smallest or largest value. If the search_type argument is 0 and no exact match is found, the function will return the #N/A! error.
- If the search_type argument is omitted, the function will assume a search_type of 1. This means that it will assume that the range is sorted in ascending order.
Examples of How to Use the MATCH Function
Here are some practical examples of how to use the MATCH function in Google Sheets.
Example #1: Simple MATCH function
Let’s say you have a list of student names in column A, and you want to find the position of the student “John” in the list. You can use the MATCH function for this. The formula would look like this:
=MATCH(“John”, A:A, 0)
This will return the row number where “John” is found in column A. If “John” is the 5th student in the list, this formula will return 5.
Example #2: Using MATCH with a dynamic lookup value
In this example, we will use the MATCH function with a dynamic lookup value. Suppose we have a list of product codes in column B, and we have the product code we want to find in cell D1. The formula would look like this:
=MATCH(D1, B:B, 0)
This will return the row number where the product code in D1 is found in column B. If the product code in D1 changes, the result of the MATCH function will also change accordingly.
Example #3: Using MATCH in a horizontal range
The MATCH function can also be used with a horizontal range. Suppose we have a row of monthly sales data in row 1, and we want to find the position of the sales data for June. The formula would look like this:
=MATCH(“June”, 1:1, 0)
This will return the column number where “June” is found in row 1. If “June” is the 6th month, this formula will return 6.
Example #4: Using MATCH with an approximate match
In this example, we will use the MATCH function with an approximate match. Suppose we have a list of numbers in column C, and we want to find the position of the number 15 or the next smallest number if 15 is not found. The formula would look like this:
=MATCH(15, C:C, -1)
This will return the row number where 15 or the next smallest number is found in column C. If 15 is not found and the next smallest number is 12, which is located at the 7th row, this formula will return 7.
MATCH: Common Mistakes & Problems
When using the MATCH function in Google Sheets, several common mistakes and problems may arise. Understanding these pitfalls can help users avoid them and use the function more effectively:
- Incorrect Data Type: The MATCH function requires the lookup value and the lookup array to be the same data type. The function will not work properly if one is a number and the other is a text.
- Non-Sorted Lookup Array: The lookup array needs to be sorted in ascending order for an approximate match. Failure to do so will result in incorrect results.
- Using Wildcards Improperly: Wildcards can be used in the lookup value, but only when the match type is set to exact match (0). If the match type is set to 1 or -1, wildcards will not work.
- Wrong Match Type: The match type argument can be -1, 0, or 1. If it’s not specified, Google Sheets assumes a value of 1. This can lead to incorrect results if the user actually wanted an exact match (0) or a less-than match (-1).
- Overlooking Errors: If the MATCH function cannot find the lookup value in the lookup array, it will return an #N/A error. This is not a glitch, but a signal that the desired value does not exist in the specified range.
- Ignoring Case Sensitivity: The MATCH function in Google Sheets is not case-sensitive. If you have case-sensitive data, you might need to use additional functions or methods to get the correct results.
Why Is MATCH Not Working? Troubleshooting Common Errors
If you’ve used the MATCH function in Google Sheets and encountered some errors, don’t fret. It’s common to run into issues, especially when you’re still getting the hang of it. Here are some of the most common errors, their causes, and how to fix them.
#N/A Error
Cause: The #N/A error usually pops up when the MATCH function cannot find the lookup_value in the lookup_array. This could be due to a typo, incorrect data type, or if the lookup_value is not in the lookup_array.
Solution: Double-check the lookup_value and ensure it exists in the lookup_array. If the data types are different, make them consistent. For example, if you’re looking for a number in an array of text strings, you’ll need to convert that number to text or vice versa.
#VALUE! Error
Cause: You’ll see this error when the search_type argument isn’t -1, 0, or 1. It can also occur when the lookup_array isn’t sorted in ascending order for a search_type of 1 or descending order for a search_type of -1.
Solution: Always ensure the search_type is set to -1, 0, or 1. Also, organize the lookup_array in the correct order based on the search_type. An ascending order for 1 and descending order for -1.
#REF! Error
Cause: This error pops up when your lookup_array refers to cells that aren’t valid. This could be because the cells have been deleted or moved.
Solution: Review the range of your lookup_array to ensure it refers to valid, existing cells. If you deleted or moved cells, adjust the range to include the correct cells.
#NAME? Error
Cause: The #NAME? error is common when Google Sheets doesn’t recognize the function name. This can happen due to a typo or if the syntax of the MATCH function is incorrect.
Solution: Ensure you’ve spelled MATCH correctly and followed the correct syntax: MATCH(lookup_value, lookup_array, [search_type]). Correct any spelling mistakes and ensure you’ve included all necessary parentheses and commas.
#NUM! Error
Cause: This error shows up when the search_type argument isn’t provided and the first value in your lookup_array is not a number.
Solution: If you don’t specify a search_type, Google Sheets assumes it’s 1 and expects the first value in your lookup_array to be a number. So, specify the search_type or ensure the first value in your lookup_array is a number.
Using MATCH With Other Google Sheets Functions
Combining the MATCH function with other Google Sheets functions can produce powerful results. Here are some examples of how you can use MATCH in combination with other functions to perform more complex tasks.
With INDEX
The INDEX function gives you the value at a specific location in a range of cells, and MATCH can help you identify that specific location.
Example: Let’s say you have a list of employees in column A and their respective salaries in column B. If you want to find the salary of a specific employee, you can use the combination of INDEX and MATCH. Assuming the employee’s name is in cell D1, the formula would be:
=INDEX(B2:B100, MATCH(D1, A2:A100, 0))
This formula would give you the salary of the employee listed in cell D1.
With VLOOKUP
The VLOOKUP function finds things in a table or a range by row. MATCH can be used to identify the column index number needed for VLOOKUP.
Example: If you have a data set of employees with their respective departments and salaries in columns A, B, and C, respectively, and you want to look up the salary of a certain employee in a specific department, you can use VLOOKUP combined with MATCH.
Assuming the employee’s name is in cell E1 and the department in cell F1, the formula would be:
=VLOOKUP(E1, A2:C100, MATCH(F1, A1:C1, 0), FALSE)
This will return the salary of the employee in the specified department.
With IFERROR
The IFERROR function returns a custom result when a formula generates an error and a standard result when no error is detected. MATCH can be used within IFERROR to handle cases when a match is not found.
Example: Using the same list of employees and salaries, if you want to look up the salary of an employee, but return “Not found” if the employee is not in the list, you can use IFERROR with MATCH.
Assuming the employee’s name is in cell D1, the formula would be:
=IFERROR(INDEX(B2:B100, MATCH(D1, A2:A100, 0)), “Not Found”)
This formula will return the salary of the employee if they are in the list and “Not Found” if they are not.
For more details on the MATCH function, check out the official documentation at the Google Docs Editors Help Center.
