Excel XMATCH Function
The XMATCH function returns the relative position of a specified value in a vertical or horizontal array or range.
Note: The XMATCH function is only available in Excel for Microsoft 365, Excel 2021 or newer versions of Excel, and Excel for the web.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Arguments
- lookup_value (required): The value you are looking for.
- lookup_array (required): The array or range of cells to search.
- [match_mode] (optional): The match type to use:
- 0 or omitted, exact match;
- -1, exact match or the next smallest value;
- 1, exact match or the next largest value;
- 2, match with wildcards *, ? and ~.
- [search_mode] (optional): The search type to use:
- 1 or omitted, search from first to last;
- -1, search from last to first;
- 2, binary search ascending - lookup_array must be sorted in ascending order.
- -2, binary search descending - lookup_array must be sorted in descending order.
Return Value
The XMATCH function returns an integer indicating the relative position of the lookup value.
Function Notes
- XMATCH returns the first match in case of duplicates.
- XMATCH is not case-sensitive. You can add the EXACT function to make a formula case-sensitive.
- lookup_array should be a one-row or one-column range.
- If the lookup value of the MATCH formula is in the form of text, enclose it in quotes.
- The #N/A error will be returned if the lookup value is not found.
Example
Let’s say you have a table that is sorted by scores, to get the rank of Kyle, please copy or enter the formula below in a cell and then press Enter to get the result:
=XMATCH("Kyle",C3:C14)
Or, use a cell reference to make the formula dynamic:
=XMATCH(H2,C3:C14)
Note: The match_mode and search_mode arguments are omitted because they default to perform an exact match and search lookup_value from first to last in the lookup_array, which perfectly suits our case here.
Example to Perform a Wildcard Match
The wildcard Asterisk (*) can match any number of characters, while the Question mark (?) matches any single character. If you need to match an actual wildcard character - Asterisk (*) or Question mark (?), put a Tilde (~) before the wildcard character.
To get the rank of the first student whose name starts with B, you should set the lookup_value to B*. Please copy or enter the formula below in a cell and then press Enter to get the result:
=XMATCH("B*",C3:C14,2)
Or, use a cell reference to make the formula dynamic:
=XMATCH(H2,C3:C14,2)
Related functions
The Microsoft Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of this value.
Excel’s new XLOOKUP is the most powerful and easiest Lookup function Excel can offer. Through unremitting efforts, Microsoft finally released this XLOOKUP function to replace VLOOKUP, HLOOKUP, INDEX+MATCH, and other lookup functions.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
