Although new dynamic array functions will get a lot of attention, it’s important to understand that dynamic array behavior is native and deeply integrated. All formulas will now run on a new calculation engine. This means that when a formula returns multiple results, these results will spill into multiple cells on the worksheet. This includes older functions and formulas not originally designed to work with dynamic arrays. For example, the LEN function returns the number of characters in a string. Traditionally, you’d use it with one cell at a time. However, with dynamic arrays, if we give the LEN function a range of values, we’ll get back multiple results. In fact, I can feed these results into another function, like SUM, to get a total of all characters in the range, with no need for intermediary formulas, and no special syntax. This a big change that can affect all kinds of formulas. For instance, the VLOOKUP function is designed to fetch a single value from a table, using a column index. With a column index of 2, I can use name to lookup the building. However, with dynamic arrays, we can ask VLOOKUP for more than one value using an array constant like this. We can lookup building and extension in a single formula. In other words, even though VLOOKUP was never designed to return multiple values, it can now do so, thanks to the new dynamic array formula engine. Let’s look at one more example. In this worksheet, the goal is to create usernames by joining the first character of the first name together with the last name. With dynamic arrays, I can use full ranges and enter the formula in one step. Notice Excel returns all results in the spill range. This formula is easy to modify as well. If I want to lowercase the usernames with the LOWER function, I only need to edit the original formula in one place. When I press enter, all results are updated. As always, these results are dynamic. If I change a name, we get a new result immediately.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
