Purpose
Return value
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"}.
Related functions
LAMBDA Function
The Excel LAMBDA function provides a way create custom functions that can be reused throughout a workbook, without VBA or macros.
LET Function
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....
MAP Function
The Excel MAP function "maps" a custom LAMBDA function to each value in a supplied array. The LAMBDA is applied to each value, and the result from MAP is an array of results with the same dimensions as the original array.
SCAN Function
The SCAN function applies a custom calculation to each element in a given array or range and returns an array that contains the intermediate values created during the scan. SCAN can be used to generate running totals, running counts, and other calculations...
REDUCE Function
The Excel REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value.
MAKEARRAY Function
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....
BYCOL Function
The Excel BYCOL function applies a LAMBDA function to each column in a given array and returns one result per column as a single array. In the example shown, data is the named range C5:H9.
Note: BYCOL is a beta function available...
BYROW Function
The Excel BYROW function applies a LAMBDA function to each row of a given array and returns one result per row in a single array. In the example shown, data is the named range C5:H15.
...
