In this post, we’ll take a quick look at some of the new features in Microsoft 365’s Excel and Excel for the Web. These are essential functions to have in your Excel toolbox.
Dynamic Arrays
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 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.
XLOOKUP Function
XLOOKUP allows you to easily look up various values in one range and return a corresponding value from another range. Unlike VLOOKUP, XLOOKUP looks both ways and is more adaptable and flexible, especially when moving ranges or inserting new columns.
Source – Microsoft
| Argument | Description |
| Lookup_value Required* | The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array |
| Lookup_array Required | The array or range to search |
| Return_array Required | The array or range to return |
| [if_not_found] Optional | Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. |
| [match_mode] Optional | Specify the match type: 0 – Exact match. If none found, return #N/A. This is the default. -1 – Exact match. If none found, return the next smaller item. 1 – Exact match. If none found, return the next larger item. 2 – A wildcard match where *, ?, and ~ have a special meaning |
| [search_mode] Optional | Specify the search mode to use: 1 – Perform a search starting at the first item. This is the default. -1 – Perform a reverse search starting at the last item. 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
When compared to VLOOKUP, XLOOKUP has some significant advantages:
- Lookup data to the right or left of lookup values with XLOOKUP.
- Multiple results can be returned using XLOOKUP
- The default setting for XLOOKUP is an exact match (VLOOKUP defaults to approximate)
- XLOOKUP can work with both vertical and horizontal data.
- A reverse search is possible with XLOOKUP (last to first)
- Not only may XLOOKUP return full rows or columns, but it can also return multiple values.
XMATCH Function
The XMATCH function locates an item in an array or range of cells, then returns its position relative to the item. You can also use XMATCH to return a value in an array. It’s similar to XLOOKUP() in that it returns the cell value, but XMatch returns a relative array position.
The required syntax for the XMATCH function is this: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | Description |
| Lookup_value Required | The array or range to search |
| Lookup_array Required | The array or range to search |
| [match_mode] Optional | Specify the match type: 0 – Exact match. This is the default. -1 – Exact match or the next smallest item. 1 – Exact match or the next largest item. 2 – A wildcard match where *, ?, and ~ have a special meaning |
| [search_mode] Optional | Specify the search type: 1 – Search first-to-last. This is the default. -1 – Search last-to-first (reverse search) 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
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)
Source – Microsoft
LET Function
The LET function is used to name the results of calculations. Within a formula, you can use this method to store intermediate calculations, values, or name definitions.
The LET function in Excel requires you to specify pairs of names, their corresponding values, and a calculated formula that uses all of them. Up to 126 name/value pairs (variables) are supported by LET, and at least one must be defined.
Source – Microsoft
Benefits
- Improved Performance – When you use the same expression in a formula numerous times, Excel calculates the result many times. LET allows you to call an expression by name and have Excel calculate it only once.
- Easy Reading and Composition – No more remembering what a specific range/cell reference meant, what your calculation was doing, or copying and pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.
Improving the Excel Experience
Since Excel doesn’t appear to be going away anytime soon, there’s a lot to be said for solutions that allow end-users to keep using Excel while also improving processes. One such platform is DataRails. DataRails FP&A solution provides organizations an Excel-based solution that improves the Excel experience while increasing security, control, and data integrity. DataRails was created to improve the tracking, control, and management of spreadsheets across an entire enterprise while maintaining the familiar Excel experience for end-users so you can keep doing what you’re doing, but better.
