Excel MATCH Function
How to use the MATCH Function
This Excel tutorial explains how to use the Excel MATCH function with syntax and examples.
SUMMARY:
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.
PURPOSE:
Get the position of an item in an array
RETURN VALUE:
The MATCH function returns a numeric value.
If the MATCH function does not find a match, it will return a #N/A error.
SYNTAX:
=MATCH (lookup_value, lookup_array, [match_type])
ARGUMENTS:
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.
REMARKS:
- 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".
When match type is set to zero, MATCH performs an exact match.
When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value.
When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value.
Basic wildcard match examples
When match type is set to zero, MATCH can perform a match using wildcards.