Spread.NET 13 WinForms introduces support for two important new Excel calculation functions: XLOOKUP and XMATCH. These new functions are recent additions to Excel.

Note: The first Spread.NET 13 service release, version 13.45.20191.1, is available today and required for the content in this blog to be up-to-date and accurate. The new release enhances the XLOOKUP function to support the new if_not_found argument. Customers who have already installed Spread.NET 13 WinForms must UNINSTALL the initial v13.45.20191.0 release using Control Panel – Programs and Features before installing this update!

XLOOKUP

The XLOOKUP function performs a lookup on the specified lookup_value in the specified lookup_array using the specified match_mode and search_mode, and then returns a value from the corresponding cell in the specified return_array.

XLookUp syntax

XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])
lookup_value The value to lookup.
lookup_array The array or range to search.
return_array The array or range to return.
if_not_found Value to return when no match is found. Note: this argument is newly added and requires the version 13.45.20191.1 service update [https://techcommunity.microsoft.com/t5/Excel-Blog/Excel-Announcements-Ignite-2019/ba-p/964482#toc-hId--898020875]
match_mode Optional value specifying the match mode: 0 - Exact match (default) 1 - Exact match or next smaller -1 - Exact match or next larger 2 - Wildcard match (where '?' represents any character and '*' represents any sequence of characters).
search_mode Optional value specifying the search mode: 1 - First to last (default) -1 - Last to first 2 - binary search on ascending lookup_array -2 - binary search on descending lookup_array * 0 - All (unique to Spread.NET)

The XLOOKUP function is a new lookup function which can replace the old LOOKUP, VLOOKUP, and HLOOKUP functions. The new function is better for several reasons:

  1. XLOOKUP can perform vertical or horizontal lookups (or both when nested), depending on the orientation of lookup_array.
  2. XLOOKUP can perform non-exact lookups with correct results, even when the data is not sorted (unlike HLOOKUP/VLOOKUP).
  3. XLOOKUP does not require referencing the entire range containing lookup_array and return_array, only those two particular ranges – thus XLOOKUP can be more efficient in terms of required recalculations.
  4. XLOOKUP arguments adjust automatically when columns or rows are inserted or removed that move the lookup_array and return_array, since it used range references instead of indexes.
  5. XLOOKUP can return 0 or some other useful value instead of #N/A when you specify the if_not_found argument.
  6. XLOOKUP is enhanced in Spread. NET 13 WinForms to support a new search_mode 0 - All which returns all matching items in an array. It can spill to adjacent cells when dynamic arrays are enabled.

To illustrate the XLOOKUP function in action, here is a sample table named XLookupData1:

New XLOOKUP and XMATCH Functions Figure 1 XLookupData1 table of sample data

Example 1 shows the most typical use case, the vertical lookup using exact match:

New XLOOKUP and XMATCH Functions Figure - 2 XLOOKUP function example 1

The XLOOKUP function searches for lookup_value (7) inside the lookup_array(XLookupData1[Number]) and returns the corresponding value from the return_array (XLookupData1[English]) using this formula:

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[English])

The other formulas work similarly, returning values from the French and Spanish columns, respectively:

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[French])

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[Spanish])

Note that unlike VLOOKUP, the default behavior of XLOOKUP performs the search using match_mode 0 - exact match and search_mode 1 - firs to last when those arguments are not specified.

Example 2 shows how XLOOKUP can perform a lookup that returns a value that is to the left or above the lookup_array. VLOOKUP and HLOOKUP cannot do this:

New XLOOKUP and XMATCH Functions Figure 3 - XLOOKUP function example 2

Example 2 uses XLOOKUP to search the Spanish column for the specified word in cell C33 and return the corresponding values from the Number, English, and French columns:

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[Number])

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[English])

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[French])

The next example uses this transpose of the XLookupData1 table to demonstrate horizontal a lookup:

New XLOOKUP and XMATCH Functions Figure 4 - Transposed table to demonstrate horizontal lookup with XLOOKUP

Example 3 shows how XLOOKUP can perform a horizontal lookup by referencing a horizontal range for lookup_array:

New XLOOKUP and XMATCH Functions Figure 5 - XLOOKUP function example 3

Example 3 uses XLOOKUP to search the Number row for the specified value in C46 and return the corresponding values from the English, French, and Spanish rows:

=XLOOKUP(C46,C40:L40,C41:L41) =XLOOKUP(C46,C40:L40,C42:L42) =XLOOKUP(C46,C40:L40,C43:L43)

This is identical to Example 1, except that the search is performed horizontally because the table is transposed.

Example 4 shows how XLOOKUP can perform a horizontal lookup and return items from rows that are above the lookup_array range:

New XLOOKUP and XMATCH Functions Figure 6 - XLOOKUP function example 4

Example 4 uses XLOOKUP to search the Spanish row for the specified value in I46 and return the corresponding values from the Number, English, and French rows:

=XLOOKUP(I46, C43:L43,C40:L40) =XLOOKUP(I46, C43:L43,C41:L41) =XLOOKUP(I46, C43:L43,C42:L42)

This is identical to Example 2, except that the search is performed horizontally because the table is transposed.

The next example uses the following table XLookupData3, a typical table listing commission rates:

New XLOOKUP and XMATCH Functions Figure 7 - XLookupData3 table of sample data

Example 5 shows how XLOOKUP can perform a search and return exact match or next smaller value:

New XLOOKUP and XMATCH Functions Figure 8 - XLOOKUP function example 5

Example 5 uses XLOOKUP to search the Sales column for the specified value in F58 using match_mode -1 - exact match or next smaller value:

=XLOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission],,-1)

The next formula is identical, except for the addition of the search_mode argument specifying 2 - binary search on ascending lookup_array. This is faster when the table is sorted, as in this case:

=XLOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission],,-1,2)

The legacy LOOKUP function can also work to find the rate in the sorted table. Since the table is organized with the lookup_array in the first column and the return_array in the last column, the formula is actually much simpler:

=LOOKUP(F58,XLookupData3)

The legacy LOOKUP function also supports specifying the lookup_array and return_array separately, like XLOOKUP:

=LOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission])

The VLOOKUP function can also do this:

=VLOOKUP(F58,XLookupData3,2)

However, when the XLookupData3 table is sorted in reverse order:

New XLOOKUP and XMATCH Functions Figure 9 - XLookupData3 table sorted in reverse

Then all the other formulas break and return the wrong result, except for the first formula using XLOOKUP:

New XLOOKUP and XMATCH Functions Figure 10 - XLOOKUP formula returns the correct result with reverse-sorted table

The next example uses the following table XLookupData4, listing various maximum numbers of units that can fit in particular sized boxes:

11 Figure 11 - XLookupData4 table of sample data

Example 6 show how XLOOKUP can perform a search and return exact match or next larger:

12 Figure 12 - XLOOKUP function example 6

Example 6 uses XLOOKUP to search the Units column for the value in F70 using match_mode 1 - exact match or next larger to find the appropriate box size:

=XLOOKUP(F70,XLookupData4[Units],XLookupData4[Box Size],,1)

The next example uses the following table XLookupData5. This table lists chess openings and associated moves:

13 Figure 13 - XLookupData5 table of sample data

Example 7 shows how XLOOKUP can perform a search using match_mode 2 - wildcard match:

14 Figure 14 - XLOOKUP function example 7

Example 7 uses XLOOKUP to search the Name column for the value in G77 using match_mode 2 - wilcard match/b> to find the associated ECO and Move(s):

=XLOOKUP(""&G77&"",XLookupData5[Name],XLookupData5[ECO],,2)

=XLOOKUP(""&G77&"",XLookupData5[Name],XLookupData5[Move(s)],,2)

Wildcard match uses '?' to represent any character and '*' to represent any sequence of characters. It applies only when match_mode 2 - wildcard match is specified.

The next example uses the following table XLookupData6. This table lists the start date, name, and department of people, which contains some duplicates:

15 Figure 15 - XLookupData6 table of sample data

The duplicate entries in the table represent people who have switched to a different department from a reorganization.

Example 8 shows how XLOOKUP can perform a search and return the last matching item:

16 Figure 16 - XLOOKUP function example 8

Example 8 uses XLOOKUP to search the Name column for the value in G87 using search_mode -1 - last to first:

=XLOOKUP(G87,XLookupData6[Name],XLookupData6[Dept],,,-1)

Returning the last item will depend on the current order of the items in the table.

XLOOKUP can also reference a range of cells or a spilled array reference for the lookup_value argument, and spill results when dynamic arrays are enabled:

17 Figure 17 - XLOOKUP referencing spilled array and spilling results

In the above example, cell F91 contains a dynamic array formula which spills results to F91:F98:

=SORT(UNIQUE(XLookupData6[Names]))

The formula in the cell G91 uses XLOOKUP with lookup_value F91# and spills results to G91:G98:

=XLOOKUP(F91#,XLookupData6[Name],XLookupData6[Dept],,,-1)

The legacy LOOKUP function can also do this:

18 Figure 18 - LOOKUP function cannot spill results and requires a much more complicated array formula

However, the LOOKUP function requires this complex array formula to return the correct result – here is the formula in cell P90:

=LOOKUP(2,1/(XLookupData6[Name]=O90),XLookupData6[Dept])

That formula is a tricky way to get the desired result. First, the lookup_value specified (2) is deliberately chosen so that no match will be found. Then, the second argument specifies 1/(XLookupData6[Name]=O90, which first evaluates the array formula part (XLookupData6[Name]=O90) to an array of True (1) and False (0) values. This specifies whether or not the particular cell in XLookupData6[Name] is equal to the specified name in O90. Then, each element is divided into 1, which results in a new array of values. It contains True (1) for each name equal to the value in O90 and #DIV/0 error values, where the previous array contained False (0) values.

The LOOKUP function will not find the lookup_value (2) in any of those array elements (by design), and it returns the value associated with the last non-error value in the array.

The formula using LOOKUP is not only more complicated, but also cannot work using a range or dynamic array argument (e.g., using O90# instead) because LOOKUP does not support dynamic arrays or spilling behavior. Instead, the formulas in the cells P91:P97 must be copied down from P90.

Example 9 shows how XLOOKUP can perform a search using search_mode 0 - all and return all the matching elements in a dynamic array:

19 Figure 19 - XLOOKUP function example 9

Example 9 uses XLOOKUP to search the Name column for the value in C102 using search_mode 0 - all:

=XLOOKUP(C102,XLookupData6[Name],XLookupData6[Start],,,0)

=XLOOKUP(C102,XLookupData6[Name],XLookupData6[Dept],,,0)

The first formula in B104 finds the start dates for Fred, and the second formula in C104 finds the associated Dept.

Note that this search_mode 0 - all is unique to Spread.NET and not supported in Excel (which will return a #VALUE! error).

XMATCH

XMatch syntax

The XMATCH function performs a lookup on the specified lookup_value in the specified lookup_array using the specified match_mode and search_mode, and then returns the index of the found item in lookup_array.

Untitled Document

XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])
lookup_value The value to lookup.
lookup_array The array or range to search.
match_mode Optional value specifying the match mode: 0 - Exact match (default) 1 - Exact match or next smaller -1 - Exact match or next larger 2 - Wildcard match (where '?' represents any character and '*' represents any sequence of characters).
search_mode Optional value specifying the search mode: 1 - First to last (default) -1 - Last to first 2 - binary search on ascending lookup_array -2 - binary search on descending lookup_array * 0 - All (unique to Spread.NET)

The XMATCH function is a new lookup function which can replace the old MATCH function. The new function is better for several reasons:

  1. XMATCH can perform vertical or horizontal lookups (or both when nested), depending on the orientation of lookup_array.
  2. XMATCH can perform non-exact lookups with correct results, even when the data is not sorted (unlike MATCH).
  3. XMATCH is enhanced in Spread.NET 13 WinForms to support a new search_mode 0 - All. It returns all matching items in an array, which can spill to adjacent cells when dynamic arrays are enabled.

To illustrate the XMATCH function in action, here is a sample table named XMatchData1:

20 Figure 20 - XMatchData1 table of sample data

Example 1 - shows the most typical use case, the vertical lookup using exact match:

21 Figure 21 XMATCH function example 1

The XMATCH function in H20 searches for lookup_value (7) inside the lookup_array (XMatchData1[Number]), and returns the index in lookup_array using this formula:

=XMATCH(H20,XMatchData1[Number])

The MATCH function in H23 does the same:

=MATCH(H19,XMatchData1[Number])

The next example uses this transpose of the XMatchData1 table to demonstrate horizontal a lookup:

22 Figure 22 - Transposed table to demonstrate horizontal lookup with XMATCH

Example 2 shows how XMATCH can perform a horizontal lookup by referencing a horizontal range for lookup_array:

23 Figure 23 - XMATCH function example 2

Example 2 uses XMATCH to search the Number row for the specified value in C38 and return the index:

=XMATCH(C38,C32:L32)

The MATCH function in C42 does the same:

=MATCH(C38,C32:L32)

The next example uses the following table XMatchData3 a typical table listing commission rates:

24 Figure 24 - XMatchData3 table of sample data

Example 3 shows how XMATCH can perform a search and return exact match or next smaller value:

25 Figure 25 - XMATCH function example 3

Example 3 uses XMATCH in F49 to search the Sales column for the specified value in F48 using match_mode -1 - exact match or next smaller value:

=XMATCH(F48,XMatchData3[Sales],-1)

The next formula in F50 is identical, except for the addition of the search_mode argument specifying 2 - binary search on ascending lookup_array. This is faster when the table is sorted, as in this case:

=XMATCH(F48,XMatchData3[Sales],-1,2)

The legacy MATCH function can also work to find the rate in the sorted table. Since the table is organized with the lookup_array in the first column and the return_array in the last column, the formula is actually simpler:

=MATCH(F48,XMatchData3[Sales])

However, when the XMatchData3 table is sorted in reverse order:

26 Figure 26 - XMatchData3 table sorted in reverse

Then, all the other formulas break and return the wrong result, except for the first formula using XMATCH:

27 Figure 27 - XMATCH formula returns the correct result with reverse-sorted table

The next example uses the following table XMatchData4 listing various maximum numbers of units that can fit in particular sized boxes:

28 Figure 28 - XMatchData4 table of sample data

Example 4 show how XMATCH can perform a search and return exact match or next larger:

29 Figure 29 - XMATCH function example 4

Example 4 uses XMATCH to search the Units column for the value in F59 using match_mode 1 - exact match or next larger:

=XMATCH(F59,XMatchData4[Units],1)

The next example uses the following table XMatchData5. This table lists chess openings and associated moves:

30 Figure 30 - XMatchData5 table of sample data

Example 5 show how XMATCH can perform a search using match_mode 2 - wildcard match:

31 Figure 31 - XMATCH function example 5

Example 5 uses XMATCH to search the Name column for the value in G66 using match_mode 2 - wildcard match:

=XMATCH(""&G66&"",XMatchData5[Name],2)

Wildcard match uses '?' to represent any character and '*' to represent any sequence of characters. It applies only when match_mode 2 - wildcard match is specified.

The next example uses the following table XMatchData6. This table lists the start date, name, and department of people, which contains some duplicates:

32 Figure 32 - XMatchData6 table of sample data

The duplicate entries in the table represent people who have switched to a different department from a reorganization.

Example 6 shows how XMATCH can perform a search and return the last matching item:

33 Figure 33 - XMATCH function example 6

Example 6 uses XMATCH to search the Name column for the value in G76 using search_mode -1 - last to first:

=XMATCH(G76,XMatchData6[Name],,-1)

Returning the last item will depend on the current order of the items in the table.

XMATCH can also reference a range of cells or a spilled array reference for the lookup_value argument and spill results when dynamic arrays are enabled:

34 Figure 34 - XMATCH referencing spilled array and spilling results

In the above example, cell F80 contains a dynamic array formula, which spills results to F80:F87:

=SORT(UNIQUE(XMatchData6[Names]))

The formula in the cell G80 uses XMATCH with lookup_value F90# and spills results to G80:G87:

=XLOOKUP(F80#,XMatchData6[Name],,-1)

Example 7 shows how XMATCH can perform a search using search_mode 0 - all and return all the matching elements in a dynamic array:

35 Figure 35 - XMATCH function example 7

Example 7 uses XMATCH to search the Name column for the value in C91 using search_mode 0 - all:

=XMATCH(C91,XMatchData6[Name],,0)

Note that this search_mode 0 - all is unique to Spread.NET and not supported in Excel (which will return a #VALUE! error).

Download XLOOKUP.xlsx

Try Spread.NET free for 30 days

Download the latest version of Spread.NET

Download Now!