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


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.

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:

=XMATCH("B*",C3:C14,2)

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

=XMATCH(H2,C3:C14,2)

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

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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