The Google Sheets MATCH Function
The MATCH function is used to find the relative position of an input string in a one-dimensional range. It does a search on each cell included, and when it discovers a match between the input string and the value stored in a cell, it outputs the relative position of the cell.
MATCH Function Syntax
The syntax of the MATCH formula Google Sheets is shown below:
=MATCH(search_key, range, search_type)
Where:
search_key
The value to search by the function.
range
The range where the function finds the string. You can only specify a single row or column as the range.
search_type
It tells Google Sheets how the entries are stored in the range cells, allowing for a faster search.
1: the entries are in ascending order
0 : the entries are not ordered; therefore an exact match must be done
-1 : the entries are in descending order.
How to use MATCH in Google Sheets
Use the MATCH function Google Sheets to search the relative position of the cell containing a specified value in the selected range cells. The value to search using the MATCH formula can be a number, a text, or a date. Check the examples below to see how the function is used for the following cases:
MATCH Function Examples:
Here are some examples of the use of the MATCH formula.
MATCH Function with Numeric Value in Search Key
In this example, you can simply type the number inside the MATCH formula without enclosing it in single or double quotes:
Or in this example, you can place the number in a separate cell and then point the MATCH formula to it:
MATCH Function with Text in Search Key
In this example, the string is enclosed in double quotes if it is inside the function:
Or in this example, you can place the text in a separate cell and then point the MATCH function to it. There is no need to enclose the text in double quotes:
MATCH Function with Date in Search Key
If you want to insert the date inside the function, you should express it using the DATE function with the following syntax:
=DATE(year,month,day)
where year, month, and day are all in numbers. It goes in the search_key part of the MATCH formula. The DATE function ensures that the input value follows the Google Sheets’ date format, making a match possible. Check this example:
Alternatively, you can place the date in a separate cell and then point the MATCH formula to it. You no longer need to use the DATE function in this case since the date is stored in the format that can be read by Google Sheets, allowing for a perfect value match. This is shown in the following example:
Common MATCH Function Issues to Avoid
Wrong search_type
If you use the wrong search_type, the function may end up giving the wrong position for a given string or even give you a position for a string that does not actually exist in the range cells. Let us have an example where the search_type is set to 1, which sets the MATCH formula to assume that the data sorted is in ascending order. Here are two ways that it doesn’t work:
In Row 8, the MATCH function is set to find the relative position of the value “John” in the column. It is in the third row but the output is 4. In Row 9, the MATCH function is set to find the relative position of the value “Joseph”, which is clearly not listed in the column. However, the output is 5.
This is because the function attribute search_type is set to 1 which is best for lists arranged in ascending order. When the list is in ascending order, MATCH formula does not need to make a perfect value match as it can accurately identify a unique enough input among the cells.
However, the list of names shown above are not in the ascending order. The function thus gives an incorrect output. If you are unsure whether the list is arranged in either ascending or descending order, it is best to set the function attribute search_key to 0, and it will look for the perfect value match to the input, as in this example:
The function gives #N/A as output for the given string that does not exist in the list. The next subsection will help you improve upon it.
The output is #N/A
One reason why you get this output is because the MATCH formula failed to find the value in the range cells. You can change this by placing the match formula inside an IFNA function, then adding a display message when it failed to make a match:
=IFNA(match(search_key,range,0),"No match")
You can see the result in the example below:
The Google Sheets INDEX Function
The INDEX formula is used to display the value of a cell at specific coordinates within a range.
INDEX Function Syntax
The syntax of the INDEX formula is as follows:
=INDEX(reference, row, column)
Where:
reference
The range of cells that will be scanned by the function.
row
The row number. The lowest number possible is 1, and the count starts from the leftmost cell. You can place here the reference to a cell containing the row number.
column
The column number. The lowest number possible is 1, and the count starts from the uppermost cell. You can place here the reference to a cell containing the column number.
INDEX Function Example
Here are two examples of the function combined in a single sheet. One is where the row and column numbers are included in the function; the other has them stored in separate cells, and then their references serving as input to the formula. The latter allows you to change the input anytime without editing the formula, ideal for creating dashboards.
Why Combine the INDEX and MATCH Function in Google Sheets?
You can combine the INDEX and MATCH formulas to create a mini-search engine in your spreadsheet where you can specify the row and column headers, and then get the corresponding value stored in the cell where they intersect. Because you know the row and column headers instead of their corresponding numbers, this plays into one’s natural way of finding specific values in a table.
The INDEX MATCH technique is a good alternative to the LOOKUP function in Google Sheets such as the VLOOKUP and HLOOKUP function. It’s because it is simpler to use, easier to implement, and gives you more flexibility and accuracy than the LOOKUP function for this purpose.
How to Combine the INDEX AND MATCH functions in Google Sheets
We will use the MATCH function to find the row and column number and then use it as input to the INDEX formula to display the value stored in the cell.
INDEX MATCH Syntax
Use the following function syntax:
=INDEX(range_of_data,MATCH(cell_containing_row,range_of_row_labels,0),
MATCH(cell_containing_column,range_of_column_labels,0))
Where:
Range_of_data: the range cells where the value is located
cell_containing_row: the cell where we can type the label we are looking for in the row
range_of_row_labels: the range where the row labels are located
cell_containing_column: where we can type the label we are looking for in the column
range_of_column_labels: where the column labels are listed
You can set the function to point to cells containing the specific values for the headers that you are looking for, thus essentially turning the function into a search engine.
The formula is case-sensitive; if you add this formula to your spreadsheet, make sure to add a note so it can be properly used.
Learn more here: INDEX MATCH Google Sheets
Examples
In this example, you can type the specific headers to the boxes:
In another example, you can set a dropdown box where you can select the row and column names.
Conclusion
In Google Sheets, the MATCH function is used to lookup the relative position of an input value in a selected range. It works by looking for the exact match to the input string. The INDEX function is used to search for the data located at the intersection of the given row and column numbers. When you combine them into the INDEX MATCH formula, you can get an improved lookup function search engine where you just need to input two identifiers to get the data you need.
Recommended articles
More resources
