“Array of arrays” is a name for a limitation of Excel’s calculation engine that can affect certain formulas that return multiple results. Excel’s current formula engine will not return an “array of arrays” and the result may be a truncated version of the data (as in the example) or an error in some cases.
“Array of arrays” is not limited to the TEXTSPLIT function , but TEXTSPLIT is an easy way to see the limitation in action. When TEXTSPLIT is used on a single cell, it returns the text split by the delimiter in a single array whose values spill onto the worksheet into multiple cells. However, when you call TEXTSPLIT on a range of cells, TEXTSPLIT returns an array of results where each result is itself an array, creating an “array of arrays”. As seen in the example shown, only the first name is returned in each row.
See this page for a workaround based on the REDUCE function .
An array operation is an operation that touches or manipulates the values in an array directly. This can be a logical comparison, a math operation, concatenation , or even feeding an array into another function. In the example shown above, the following formulas appear, each representing a different type of array operation:
=B5:B13>6 // logical comparison
=B5:B13+1 // math operation
=B5:B13&"x" // concatenation
=INT(B5:B13) // another function
Array formulas include one or more array operations. The final result of an array formula can be a single value or multiple values.
Array operations and Excel 365
The distinctive feature of an array formula is that it includes an array operation, and there is a big difference in how array operations are handled in the current version of Excel versus older versions. In Excel 2019 and older, many formulas with array operations had to be entered with control + shift + enter to work properly. In Excel 365, arrays are native , and this is not necessary. In Legacy Excel , there are four functions that can natively handle array operations in an argument : SUMPRODUCT , LOOKUP , AGGREGATE , and INDEX *. These functions can be used to create array formulas that work without control + shift + enter in older versions of Excel.
Example
For example, the formula in D5 is:
=B5:B13>6
This is an array operation below compares values in the range B5:B13 to 6 with the greater than operator (>):
=B5:B14>6
This is a logical expression with an array that comes from the range B5:B13. Expanded, we have:
{3.3;6.1;9.7;7.2;2.3;6.2;4.9;5.4;9.7}>6
Because there are 9 cells in B5:B13, the result is an array with 9 TRUE or FALSE values:
{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}
