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: This is an array operation below compares values in the range B5:B13 to 6 with the greater than operator (>): This is a logical expression with an array that comes from the range B5:B13. Expanded, we have: Because there are 9 cells in B5:B13, the result is an array with 9 TRUE or FALSE values:
Author
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.
