The following examples show you how to create multi-cell and single-cell array formulas. Where possible, we’ve included examples with some of the dynamic array functions, as well as existing …

Create array formulas, often called Ctrl Shift Enter or CSE formulas, to perform calculations that generate single or multiple results.

Formulas that can return arrays of variable size are called dynamic array formulas. Formulas that are currently returning arrays that are successfully spilling can be referred to as spilled array formulas.

Use array constants in array formulas or by themselves to save time, and when you need values that aren't in your workbook.

In September, 2018 we introduced dynamic array formulas. Dynamic array formulas can automatically populate or "spill" into neighboring blank cells, and eliminate the need for legacy Ctrl+Shift+Enter …

In September, 2018, we released dynamic array formulas for Excel for Microsoft 365. The differences between dynamic arrays and legacy Ctrl+Shift+Enter (CSE) formulas are discussed below.

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

To change or edit an array formula, you need to follow some rules, especially if you have multi-cell formulas. This article spells out the rules.

If an array has fewer columns than the maximum width of the selected arrays, Excel returns a #N/A error in the additional columns. Use VSTACK inside the IFERROR function to replace #N/A with the value …

Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (select Table Design > Tools > Convert to range).