Excel MATCH Function
How to use the MATCH Function
This Excel tutorial explains how to use the Excel MATCH function with syntax and examples.
The MATCH function searches for a value in an array and returns the relative position of that item.
The MATCH function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MATCH function can be entered as part of a formula in a cell of a worksheet.
Get the position of an item in an array
The MATCH function returns a numeric value.
If the MATCH function does not find a match, it will return a #N/A error.
=MATCH (lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
- lookup_value Required. The value you want to find. It can be a numeric, text or logical value as well as a cell reference.
- lookup_array Required. A range of cells that contains the value that you are searching for.
- match_type Optional. It defines the match type. It can be one of these values: 1, 0, -1. The match_type argument set to 0 returns only the exact match, while the other two types allow for approximate match.
- If match_type is 1 : MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
- If match_type is 0 : MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
- If match_type is -1 : MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
- If match_type is omitted : it is assumed to be 1. Note: All match types will find an exact match.
- Match is not case-sensitive.
- Match returns the #N/A error if no match is found.
- The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
- If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. For Example: The condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".