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.
Sometimes you have repeating values in the column and you want to extract only their unique instances. The Remove Duplicates option is used to do this, and the UNIQUE is a new Excel function that has been built to perform the same thing. It generates a dynamic array with unique values, whether it contains text, numbers, dates …