Summary

The Excel MAKEARRAY function returns an array with specified rows and columns, based on a custom LAMBDA calculation. MAKEARRAY can be used to create arrays with variable dimensions based on a calculation.

Purpose 

Create array with calculated values

Return value 

An array of calculated values

Syntax

=MAKEARRAY(rows,columns,function)
  • rows - The number of rows to create.
  • columns - The number of columns to create.
  • function - The custom LAMBDA calculation to apply.

How to use 

The MAKEARRAY function returns an array with specified rows and columns, based on a custom LAMBDA calculation. MAKEARRAY can be used to create arrays with variable dimensions that are calculated. The generic syntax for MAKEARRAY looks like this:

=MAKEARRAY(rows,columns,function)

The MAKEARRAY function takes three arguments: rows, columns, and function. Rows is the number of rows to create, and columns is the number of columns to create. The function is a custom LAMBDA (see below) 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 function used to calculate array values. The structure of the LAMBDA used by MAKEARRAY is:

LAMBDA(r,c,calculation)

where r represents the row count, 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 a hardcoded value as well. Below are examples of the same formula, with calculation hardcoded as zero and "x", respectively:

=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"}.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.