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. Download our examples

Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling . Formulas that can return arrays of variable size are called dynamic array formulas.

Excel provides two types of array formulas: Array formulas that perform several calculations to generate a single result and array formulas that calculate multiple results. Some worksheet functions return arrays of values, or require an array of values as an argument.

Below is a list of functions that could return multi-cell ranges or arrays in what we refer to as pre-dynamic array Excel. If these functions were used in workbooks predating dynamic arrays, and returned a multi-cell range or array to the grid (or a function that did not expect them), then silent implicit intersection would have occurred.

There are now 14 new text and array functions in Excel 2024 and Excel 2024 for Mac that are designed to help you manipulate text and arrays in your worksheets. These functions make it easier to extract and split text strings and enable you to combine, reshape, resize, and select arrays with ease.

There are two ways to use LOOKUP: Vector form and Array form. Vector form: Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match. For example, if you want to search for a value in column A, down to row 6.

Lists all Excel functions by their category, such as Logical functions or Text functions.

Use VLOOKUP, HLOOKUP, and other functions like INDEX and MATCH to search for and find data based on values you enter. This article gives you a quick VLOOKUP refresher, then links to more.

Returns a calculated array of a specified row and column size, by applying a LAMBDA function. Syntax =MAKEARRAY(rows, cols, lambda(row, col)) The MAKEARRAY function syntax has the following arguments and parameters: rows The number of rows in the array. Must be greater than zero. cols The number of columns in the array. Must be greater than zero.

This example uses the INDEX function in an array formula to find the values in two cells specified in a 2x2 array.