Skip to main content

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.

xmatch function 1


=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])


  • 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.


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:


Or, use a cell reference to make the formula dynamic:


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.

xmatch function 2

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:


Or, use a cell reference to make the formula dynamic:


xmatch function 3

Related functions

Excel MATCH Function

The Microsoft Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of this value.

Excel XLOOKUP Function

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

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations