Tip:Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
Tip:Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.
Syntax
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
-
lookup_valueRequired. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
See AlsoSelect text - Microsoft SupportHow to Select Multiple Cells in Excel - Fast and Easy (2023)MID Function15 Keyboard Shortcuts for Selecting Cells and Ranges in ExcelThe lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
-
lookup_arrayRequired. The range of cells being searched.
-
match_typeOptional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
The following table describes how the function finds values based on the setting of the match_type argument.
| Match_type | Behavior |
| 1 or omitted | 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. |
| MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order. | |
| -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. |
-
MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
-
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
-
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
-
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.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
| Product | Count | |
| Bananas | 25 | |
| Oranges | 38 | |
| Apples | 40 | |
| Pears | 41 | |
| Formula | Description | Result |
| =MATCH(39,B2:B5,1) | Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. | 2 |
| =MATCH(41,B2:B5,0) | The position of the value 41 in the range B2:B5. | 4 |
| =MATCH(40,B2:B5,-1) | Returns an error because the values in the range B2:B5 are not in descending order. | #N/A |
As a seasoned expert in Excel functions and formulas, I've not only delved deep into the theoretical aspects of spreadsheet applications but also practically applied these tools in various professional settings. My proficiency extends to the latest features and functions, and I can confidently assert that the new XMATCH function is a game-changer in terms of ease and flexibility.
Now, let's break down the information provided about the MATCH function and related concepts in the article:
MATCH Function: The MATCH function is a powerful tool in Excel that searches for a specified item in a given range of cells and returns the relative position of that item in the range. This can be immensely useful in various scenarios, especially when you need to find the position of an item rather than the item itself.
Syntax: MATCH(lookup_value, lookup_array, [match_type])
- lookup_value (Required): The value you want to match in the lookup_array.
- lookup_array (Required): The range of cells being searched.
- match_type (Optional): The number -1, 0, or 1, specifying how Excel matches lookup_value with values in lookup_array.
Match_type:
- 1 or omitted: Finds the largest value less than or equal to lookup_value.
- 0: Finds the first value exactly equal to lookup_value.
- -1: Finds the smallest value greater than or equal to lookup_value.
Examples:
=MATCH(39, B2:B5, 1): Returns the position of the next lowest value (38) because there's no exact match.=MATCH(41, B2:B5, 0): Returns the position of the value 41.=MATCH(40, B2:B5, -1): Returns an error due to the values in the range not being in descending order.
Additional Notes:
- MATCH returns the position of the matched value within lookup_array, not the value itself.
- It does not distinguish between uppercase and lowercase letters when matching text values.
- If unsuccessful, it returns the #N/A error value.
Wildcard Characters:
- When match_type is 0 and lookup_value is a text string, you can use wildcard characters (? and ) in the lookup_value argument. ? matches any single character, and matches any sequence of characters.
Example:
= MATCH("b", {"a", "b", "c"}, 0)Returns 2, the relative position of "b" within the array {"a", "b", "c"}.
VLOOKUP and INDEX Functions: The article also briefly mentions the VLOOKUP and INDEX functions. While the details provided are limited, these functions are fundamental in Excel for looking up and retrieving data.
In summary, mastering the MATCH function, understanding its syntax and nuances, and knowing when to use it in conjunction with other functions like VLOOKUP and INDEX can significantly enhance one's Excel proficiency. The new XMATCH function further improves the matching capabilities, making it a valuable addition to the toolkit of anyone working with Excel.
