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 array formulas entered as both dynamic and legacy arrays.
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.
When you enter an array formula, you most often use a range of cells in your worksheet, but you don't have to. You can also use array constants, values you just enter in the formula bar inside braces: {}.
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).
Select the cell or range of cells that contains the formula. If the formula is an array formula, you must first select all cells in the range of cells that contains the array formula:
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.
