How to Use XMATCH Function in Excel


The XMATCH function in Excel is use to find the position of an ranges in vertical or horizontal. XMATCH support currently support exact match, approximates match, reverse search, and wildcards.


Lookup and returns the positions of an range or table


= XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
XMATCH Function in Excel


lookup_value: The lookup value
lookup_array: The range or array where to look
match_mode: Optional
search_mode: Optional

Example Explanation

In the above mentioned example we find out the position of Sub Category “Mobile”. In our example H5 is lookup value and C6:C9 is lookup range. Formula used in H6 is:

Match Type

The third argument of XMATCH is match_type. It is optional argument that works as follows:
Match TypeFunctionality
0 (Default)Exact match
– 1Next smaller item
1Next larger item
2Wildcard match (*,?,~)

Search Mode

The fourth argument of XMATCH is search_mode. It is optional argument that works as follows:
Search modeFunctionality
1 (Default)Search from first value
– 1Search from last value
2Binary search values sorted in ascending order
– 2Binary search values sorted in descending order

You can rate this function below!

Rate XMATCH Function
User Review
0 (0 votes)
scroll to top