MATCH formula in Excel returns the relative position of a value in an array or a range
MATCH Formula Syntax
MATCH Formula has three parts:
MATCH (value_to_find, range_to_find_value_in, match_type)
value_to_find is the value which we are trying to find. It can be a number or a string. If the value_to_find is specified as a string, you can use special wildcard characters to specify the string. (For Single character use ? and for multiple characters use *)
range_to_find_value_in is the range in which we would like to locate the value in. This range has to consist of cells in a single continuous row of column. A 2-dimensional range will not generate the correct result.
rmatch_type is an optional parameter that specifies how we would like the match function to operate. This parameters can have three potential values -1, 0 and 1. If the value is specified as -1 then the function would try to find the smallest value in the range that is greater than or equal to the value we are trying to find. For this parameter to work correctly, the range has to be sorted in the descending order. If the value is specified as 1 then the function would try to find the largest value in the range that is less than or equal to the value we are trying to find. For this parameter to work correctly, the range has to be sorted in the ascending order. If the match_type parameter is omitted, it is assumed to be 1. Finally if the parameter is specified as 0, the first exact match to the value is returned, irrespective of the sort order of the range. (Please note that this parameter can also be a boolean with TRUE for an approximate match and FALSE for an exact match).
Also worth noting is that if a match is found, the formula returns the relative position of the value in the range which means that the position is specified with respect the first cell of the range specified and not the first cell in the spreadsheet (A1). So incase you plan to use this formula with any other function, one needs to be aware of this.
Example of a MATCH Formula
Let’s us take a look at an example of the MATCH formula. Suppose we had a list of managers (names) for a business as shown in the above example. And now let’s say that we wanted to find the position at which a specific manager’s name (Say “Bill”) appears in the list. We could simply enter =MATCH(“Bill”,A1:A10,0) or=MATCH(“Bill”,A1:A10,-1). The formula mentioned first will go ahead and look for an exact match for “Bill” and if an exact match is found, will return the position (relative to the first cell in the range). The second would have tried searching for a match which is the closest value less than or equal to the value specified. Hence the chances of finding a match, all else being equal, are higher when using the later function albeit at the cost of lower accuracy.
MATCH formula with the single character (?) and multiple character (*) wildcard operators
Let’s now take the example where you would like to know the position of the first occurrence of name starting with a ‘j’ with an ‘m’ as the third character. We have two such names in the list – Jim and James. Aplhabetically James appears earlier but in our list Jim is listed at position 3 and James at 5. Now if we were to use the formula = =MATCH(“j?m”,A2:A10,0) we would get the 3 as the result. Why? Because we specified the single character wildcard (?) and 0 as the third parameter. The ? wildcard character will give a match for any single character at that position. The 0, as we’ve seen earlier, works to find and exact match.
Now say we wanted to find any name with the substring “rri” in it. We have two such names “Terri” and “Sherri” with Terri appearing first. We could use the multiple character wildcard operator (*) and write something like =MATCH(“*rri”,A2:A10,0). What we’ve done here is to specify the function to find the first occurrence where the substring “rri” occurs anywhere in the name. In this case the result will be 1 since “Terri” is a name that occurs in the first cell with the given substring. You can combine both the wildcard characters and write something like =MATCH(“?e*ri”,A2:A10,0) as a variation.
How to enter the MATCH formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =MATCH(
3. Then enter the value that you would like to find. This can be a number, a string, a cell address or a combined expression with a wildcard character as shown above.
4. Press the comma key (,)
5. Then using the mouse up-down and left-right keys, move to the first cell in range in which you would like to locate this value.
6. Keeping the SHIFT key pressed, move the cursor to the last cell of the range.
7. At this point you can either close the formula by entering the closing bracket ) enter the optional third parameter. If you want to enter the third parameter, read on.
8. Press the comma key (,)
8. Enter a value from (-1,0,1) as the match_type parameters. (0 for an exact match, -1 and 1 for approximate matches as described above)
9. Close the formula by entering the closing bracket ).
Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =MATCH(“jim”,A2:A10,0)
Possible Errors with the MATCH Formula
The MATCH formula can result in the following error values:
MATCH Formula #NAME? Error
If the range or a parameter has been incorrectly specified, the MATCH formula can result in the #NAME? error. For Example specifying =MATCH(“?e*ri”,A2:AAAA10,1) would lead to this error since the cell AAAA10 does not exist in the spreadsheet. Similarly specifying the match_type parameter as a string can lead to this error.
MATCH Formula #NA Error
If the MATCH formula does not find a match, it will generate the #N/A error value.
You can download an example of MATCH formula here or click on the button below: