One of the earlier “recipes” mentioned categories and subcategories. If you remember, we wanted to make it possible to automatically create a list with a selection of subcategories by selecting a category from the list generated with Data Validation. We could have done this by applying a few tricks, but the list, in addition to the necessary values, contained “empty slots”. This can be easily avoided today with the help of dynamic arrays.
The XMATCH function is a more modern version of the MATCH function, and is used to search arrays, by row or column, to find the position of a given value. It supports accurate and approximate comparisons, and wildcard characters can be used as a substitute for one (?) or more characters (*) during search. This is another of the new dynamic array features that is available exclusively to Office 365 subscribers as of February this year.
Any more experienced user often uses the VLOOKUP function and is aware of its features and drawbacks. Therefore, the XLOOKUP function was created which can replace the VLOOKUP, HLOOKUP and LOOKUP functions. It also supports wildcard characters used to replace multiple (*) or single character (?) which makes it very flexible and probably your choice for some future spreadsheet calculations.
SORTBY is another of the new dynamic array functions that, unlike the SORT function, arange data within arrays based on other given arrays in ascending or descending order. The application of this function is that you can sort the given range first by the criteria in one column and then by those in another. Let’s see how this works!
SORT is a new Excel function that generates a dynamic array of sorted values based on specified criteria. It allows us to specify the range of values to be sorted, to select columns, sorting order as well as how the data is being recorded, i.e. whether the sorting is done according to the data contained in the rows or according to those contained in the columns.