Exceljet

Quick, clean, and to the point

Excel MAKEARRAY Function

Excel MAKEARRAY function
Summary 

The Excel MAKEARRAY function returns a array with specified rows and columns, based on a custom LAMBDA calculation. MAKEARRAY can be used to create arrays with variable dimensions where values are the result of a calculation.

Purpose 
Create array with calculated values
Return value 
An array of calculated values
Syntax 
=MAKEARRAY (rows, columns, lambda)
Arguments 
  • rows - The number of rows to create.
  • columns - The number of columns to create.
  • lambda - The custom LAMBDA calculation to apply.
Version 
Usage notes 

The MAKEARRAY function returns a array with specified rows and columns, based on a custom LAMBDA calculation. MAKEARRAY can be used to create arrays with variable dimensions where the values in the array are calculated.

The MAKEARRAY function takes three arguments: rows, columns, and lambda. Rows is the number of columns to create, and columns is the number or columns to create. Lambda is the calculation to use when creating values in the array. The total number of values in the array returned by MAKEARRAY will equal rows * columns. 

LAMBDA structure

The MAKEARRAY uses the LAMBDA function to apply the formula required to calculate array values. The structure of the LAMBDA used by MAKEARRAY is:

LAMBDA(r,c,calculation)

where r represents the row count, and c represents the column count originally passed into MAKEARRAY, and calculation is the formula needed to create the values in the final array.

Note: to generate an array with sequential values, see the SEQUENCE function.

Examples

In the formula below, MAKEARRAY is used to create an array with 2 rows and 3 columns, populated with the result multiplying rows by columns:

=MAKEARRAY(2,3,LAMBDA(r,c,r*c)) // returns {1,2,3;2,4,6}

The result is a 2 x 3 array with six values {1,2,3;2,4,6}.

The calculation can be hardcoded. Below are examples of the same formula, with calculation hardcoded as zero and "x":

=MAKEARRAY(2,3,LAMBDA(r,c,0)) // returns {0,0,0;0,0,0}
=MAKEARRAY(2,3,LAMBDA(r,c,"x")) // returns {"x","x","x";"x","x","x"}

Random values

MAKEARRAY can be used to generate random values. In the formula below. The CHAR function is used with the RANDBETWEEN function to generate random uppercase letters A-Z: 

=MAKEARRAY(2,3,LAMBDA(r,c,CHAR(RANDBETWEEN(65,90))))

The result is a 2 x 3 array like: {"D","Q","F";"V","C","T"}.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.