Using the Excel MATCH Function
SummaryThe MATCH function will search a user provided range for a specified value. Upon finding the value, the function returns the value's position within the range. For example, if the range A1:A5 contains the names Bob, Bill, Jim, Jake, and Mike, then the formula =MATCH("Jim", A1:A5, 0) returns 3 since "Jim" is in the third position in the range.
The MATCH function is useful when you need to find a value's position within a range. If you need to find the value itself, consider using LOOKUP, VLOOKUP, or HLOOKUP.
Syntax=MATCH (lookup_value, lookup_array, [match_type])
Syntax BreakdownLookup Value
Required. The value that you would like to match in the lookup_array argument. Lookup_value can be a value, such as a number, text or logical value, or a cell reference to a number, text, or logical value.
Required. The range of cells that will be searched.
Optional. This argument allows you to control how Excel matches lookup_value with the values in lookup_array. The default is 1 and options include -1, 0, and 1.
- Using -1 (or leave match_type blank) instructs the function to find the largest value that is less than or equal to lookup_value. Values in lookup_array must be sorted in ascending order (i.e. -1, 0, 1 and A-Z);
- Using zero (0) instructs the function to find the first value that is an exact match to lookup_value. Values in lookup_array can be in any order;
- Using 1 instructs the function to find the smallest value that is greater than or equal to lookup_value. Values in lookup_array must be sorted in descending order (i.e. 1, 0, -1 and Z-A).
Usage NotesMATCH is used when you need to locate the position of a value in a range of cells. The function supports exact and approximate matching as well as the use of wildcards. Its ability to use various matching modes makes the MATCH function more flexible than other lookup functions in Excel.
MATCH does not return the value itself, only the value's position in the range. Use one of the lookup functions, such as VLOOKUP or HLOOKUP, to return the value.
When matching text values, the function is not case sensitive. For example, =MATCH("Bill", A1:A5, 0) will have the same output as =MATCH("BILL", A1:A5, 0).
The function will return the #N/A error if no match can be found.
MATCH and Wildcard Characters
The MATCH function supports the use of wildcard characters. Use the question mark (?) to match a single character and the asterisk (*) to match any sequence of characters.
If you need to find an actual question mark or asterisk, enter the tilde (~) before the character.