EXCEL function
XMATCH
for Excel 2007 to 2019
Download the Excel file with the VBA code of the functions
SORT, SORTBY, FILTER, XLOOKUP, XMATCH, UNIQUE, SEQUENCE, RANDARRAY
and all examples shown on these web pages:
▸Download Excel file
(Version 3.7 - Last update: 2023-01-06 - XLookup and XMatch now fit for use in array formulas ▸more on this)
What is it about?
In Excel 365 and from Excel 2021, Microsoft implemented the long-missing cell functions SORT, SORTBY, FILTER, XLOOKUP and others. However, they were not retrofitted for older versions of Excel, so that all users from Excel 2007 up to and including Excel 2019 had to do without them.
There is now a solution for users of older versions of Excel (since the end of January 2022).
With the help of VBA so-called UDFs (user defined functions) were implemented that have exactly the same parameter lists as the corresponding Microsoft functions.
In cases where the functions provided here return multiple values, their input must be terminated with CTRL-SHIFT-ENTER. Before entering the formula, an area must be selected on the spreadsheet that contains the returned data - as is usual with the so-called CSE array formulas (CSE = Control Shift Enter).
▸This tutorial provides a basic and detailed introduction to array formulas.
With Function names of UDFs it doesn't matter whether the letters are written in upper or lower case.
For instructions on integrating the functions in your own Excel file, see ▸Integrating the functions.
XMATCH function
The XMATCH function is an improvement on the previous MATCH function. It fulfills their tasks and also offers other options. It searches for an element in a list in exactly the same way as the XLOOKUP function, but instead of returning values found, it returns the relative position of the element found in the list.
Syntax
= XMatch ( lookup_value; lookup_array; [match_mode]; [search_mode] )
| Parameter | Explanation |
|---|---|
| lookup_value | Value to search for If this parameter is omitted (just a comma), an empty cell is searched for |
| lookup_array | Array or range to search |
| match_mode (optional) |
Match type: 0: Exact match search (default) 1: Exact match search If none is found, the next larger value is returned -1: Exact match search If none is found, the next smaller value is returned 2: Search with wildcard symbols The wildcards *, ?, ~ can be used for the search |
| search_mode (optional) |
Search mode: 1: Normal search starting with the first item (default) -1: Reverse search starting with the last item 2: Fast binary search that requires the lookup array to be sorted in ascending order -2: Fast binary search that requires the lookup array to be sorted in descending order |
The XMatch function has the same behavior as the Excel 365 function XMATCH.
See also the function description from Microsoft:
▸https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
The new XLOOKUP function has significantly more options than the VLOOKUP function. The last two parameters alone theoretically allow 4 ✕ 4 = 16 different combinations. In practice, there are only 14 different possibilities. The reason: If the comparison mode parameter is equal to 2 (wildcard search), search modes 1 and 2 as well as search modes -1 and -2 have the same effect.
Example:
Since the last two optional parameters are omitted, the default values are used (exact match search and normal search order starting with the first item in the list).
New possibilities of the function:
- The list can also be searched in reverse order so that the last occurrence of the search item is found.
- You can choose whether to search an unsorted list (search mode 1 and -1) or a sorted list (search mode 2 and -2). Even with an unsorted list, the next larger or next smaller element is found.
- You can now also search for empty fields (leave first parameter unspecified).
The search in an unsorted list has a speed disadvantage, because the search in a sorted list according to the so-called binary search method (search mode 2 or -2) is much faster.
Binary search works like number guessing, where you look for a number between 1 and 1000:
You choose the middle as the first number, i.e. 500, ask whether this is the number you are looking for and then you are told that the number you are looking for is larger. Then you choose the middle again from the range 501 to 1000, i.e. 750, and get the feedback that the number is smaller. If you continue this in the same way, you will have found the number you are looking for at the latest after 10 questions (and comparative feedback).
With an unsorted list of 1000 elements, you have to compare all values in the list one after the other (sequential search). In the worst case you have 1000 comparisons, on average about 500 comparisons.
Sharing knowledge is the future of mankind