Dynamic arrays

Dynamic arrays make it easy to work with multiple values in a single formula. This dynamic behavior means that, unlike classic array formulas, the result automatically adapts: if the size of the input array or a function's arguments changes, the size of the resulting array changes as well. There's no need to re-enter the array formula when the input and output data grows or shrinks.

Dynamic arrays are produced by the results of the functions FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY, UNIQUE, XLOOKUP, XMATCH.

These functions return an array of data automatically, so there is no need to enter them using the Ctrl+Shift+Enter keyboard shortcut.

Dynamic array spilling

The dynamic array is said to spill in the spreadsheet when data is inserted as the result of the above functions. You can reference the resulting array data by writing the reference of the first element of the resulting array suffixed by the spill range symbol #.

• Range A2:A11 is the source data.

• Range B2:B11 is a dynamic array spilled as result of the SORT function. Every cell in B2:B11 has formula =SORT(A2:A11).

• Range C2:C11 is a dynamic array referenced the # spill suffix to the range B2:B11. Every cell in the range C2:C11 has formula =B2#

• Cell D2 shows reference error #REF! since range A2:A11 is not a dynamic array.

• Range E2:E11 is also a dynamic array referencing another dynamic array.