Summary
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.
Purpose
Lookup and returns the positions of an range or table
Syntax
= XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
Arguments
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 Type | Functionality |
| 0 (Default) | Exact match |
| – 1 | Next smaller item |
| 1 | Next larger item |
| 2 | Wildcard match (*,?,~) |
Search Mode
The fourth argument of XMATCH is search_mode. It is optional argument that works as follows:
| Search mode | Functionality |
| 1 (Default) | Search from first value |
| – 1 | Search from last value |
| 2 | Binary search values sorted in ascending order |
| – 2 | Binary search values sorted in descending order |
You can rate this function below!
