Excel Function: MAKEARRAY

The function MAKEARRAY returns an array of the specified dimensions by applying a LAMBDA function.

Usage:

=MAKEARRAY(rows, columns, LAMBDA)

Example of use

The goal here is to generate a 10x10 array numbered from 1 to 100 (by columns) using a single formula.

Enter the MAKEARRAY function followed by the number of rows and columns of the array to generate:

=MAKEARRAY(10,10

Then add the LAMBDA function and choose two names for the row and column number variables:

=MAKEARRAY(10,10,LAMBDA(row,column

And finally, add the useful formula to number from 1 to 100 (one column after the other):

=MAKEARRAY(10,10,LAMBDA(row,column,row+10*(column-1)))

The MAKEARRAY function then generates a 10-row and 10-column array and returns values that are calculated based on their position (row / column) in the array: