Last update: 08.06.2022 
Excel Logo hb

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.4 - Last update: 2022-03-15)

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 intergrating 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:

Example for the function XMATCH

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 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.

Ornament

Sharing knowledge is the future of mankind