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.
* Source: Mike Girvin in his book Control + Shift + Enter
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}
