Excel XMATCH Function

The XMATCH function returns the relative position of a specified value in a vertical or horizontal array or range.

Syntax

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

Arguments

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