In addition to the general Office 2021/LTSC features there are some useful additions to Excel 2021/LTSC including the dynamic array functions, improved XLookup()/XMatch() and Let() but sadly no Lambda().
All these features are in Excel 2021 and Excel LTSC for both Windows and Mac in addition to the overall updates that apply to all the Office 2021/LTSC apps, see New and better features in Office 2021 and Office LTSC
An array of values can be returned by using a single formula. Excel 2021/LTSC have the core dynamic array formulas: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and RANDARRAY.
FILTER() filter a table, range or array to show only some items from the source.
SORT() to sort a table, range or array.
SORTBY() sorts a table, range or array based on a one part of the source. For example, sort a list of class test results by student scores.
UNIQUE() a list of values without any duplicates.
SEQUENCE() makes series of numbers (e.g. 1, 2, 3) across multiple columns or rows. Seemingly irrelevant, Sequence() can be combined with Index() to make ‘Top 5’ or ‘Bottom 10’ filtered lists.
RANDARRAY() makes random numbers across many columns or rows.
By using the XLOOKUP function, you can find items in a table or range by row. You can, for instance, look up the price of a part by its part number, or find a person’s name by their employee ID. It allows you to look up a search term in one column and then return a result in another column derived from the same row.
The XLOOKUP function can return multiple items, for example, it can return the name of the employee and department from cell C5:D14 with a single formula. It’s much easier and better than the old VLOOKUP/HLOOKUP options.
The XMATCH function locates an item in an array or range of cells, then returns its position relative to the item. Additionally, you can use XMATCH to return a value in an array. It’s similar to XLookup() which returns the cell value while XMatch returns a relative position in the array.
For example, if you want to find the position of an item on a list, in the below example the formula is: =XMATCH(E3,C3:C7)
The LET function names the results of calculations. Using this method, you can store intermediate calculations, values, or name definitions within a formula.
Excel’s LET function requires you to specify pairs of names, their corresponding values, and a calculated formula using them all. LET supports up to 126 name/value pairs (variables), and at least one must be defined.
Alas, no Lambda()
Sadly, the really useful Lambda() function has NOT made it to Excel 2021/LTSC.
Microsoft says that Excel 2021/LTSC has “faster calculations from common Excel functions like SUMIF, COUNTIF, and AVERAGEIF” which sounds like the improvements made in Excel 365 version 2005 to include an internal cached index of the target range.
That’s in addition to the general performance improvements promised for all Office 2021/LTSC apps.