Build 4.5.0.74 of xlsgen adds read, write and calculation support for XMATCH() and XLOOKUP() functions.

Those functions have been recently introduced by Microsoft as part of Office 365 insider preview and they'll be made available with an Office 365 subscription in 2020. They will never be supported in any existing Excel version (2007/2010/2013/2016/2019).

xlsgen supports them right now.

XMATCH() and XLOOKUP() are, according to Microsoft, more powerful versions of MATCH() and LOOKUP() functions.

They are indeed more powerful in the sense that MATCH() has several lookup algorithms, it does not go only top to bottom. And XLOOKUP() is more powerful than traditional LOOKUP()/HLOOKUP()/VLOOKUP() functions in the sense that the same function supports looking up in all directions, also it has a "if not found" fallback parameter which can be set optionally, and finally the return range is arbitrarily set. In other words, XMATCH() and XLOOKUP() can replace complex combinations of simpler functions sometimes.

Syntax : (optional arguments are square braced)

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])


match_mode is {0 = exact, 1 = lesser approximation, -1 = higher approximation, 2 = exact with wildcard characters}

search_mode is {1 = top-to-bottom search, -1 = bottom-to-top, 2 = half split top to bottom, -2 = half split bottom to top}