This function returns an array of specified dimensions with values calculated by application of a LAMBDA function.
Sample Usage
MAKEARRAY(2, 3, LAMBDA(row_index, column_index, row_index+column_index))
MAKEARRAY(2, 3, LAMBDA(row_index, column_index, row_index*column_index))
Syntax
MAKEARRAY(rows, columns, LAMBDA)
rows
: The number of rows to return.columns
: The number of columns to return.LAMBDA
: ALAMBDA
that’s applied to create the array. Syntax:
LAMBDA(name1, name2, formula_expression)
 Requirements:
 The
LAMBDA
must have exactly 2name
arguments along with aformula_expression
which uses thosenames
. When applying theLAMBDA
,name1
resolves to the currentrow_index
andname2
resolves to the currentcolumn_index
.
 The
 Syntax:
Notes

The passed
LAMBDA
function should accept exactly 2name
arguments, otherwise an#N/A
error is returned. These correspond torow_index
andcolumn_index
, in order. These are explained as:name1
: Resolves to the currentrow_index
for which value is created.name2
: Resolves to the currentcolumn_index
for which value is created.
 Every value created by the
LAMBDA
function applied on indices should be a single value. Array results for created values aren’t supported. row_index
andcolumn_index
start from 1. A
named function
can be passed for theLAMBDA
parameter and behaves like aLAMBDA
function in this case. Learn more about named functions. The
named function
must follow theLAMBDA
syntax forMAKEARRAY
with exactly 2 argument placeholders defined for it.  The
named function
shouldn't be followed by parenthesis.
 The
Examples
Return a 2 by 3 array with row index*column index as cell value
Example: =MAKEARRAY(2, 3, LAMBDA(row_index, column_index, row_index*column_index))
Result:
1 
2 
3 
2 
4 
6 
Return a 2 by 3 array with random numbers between 1 and 100
Example: =MAKEARRAY(2, 3, LAMBDA(row_index, column_index, RANDBETWEEN(1,100)))
Result:
53 
70 
38 
6 
47 
78 
In this example, the function makes a randomized 2 by 3 array, with a number between 1 and 100 for each cell.
Use a named function as LAMBDA to return a 4 by 4 array
Use a named function
as LAMBDA
to return a 4 by 4 array with data in waterfall format. Where the value of an additional row equals the value of the previous row +1, given that the starting value is 100 in row 1.
Named function: WATERFALL
is a named function
which outputs a value of 100 + row_index
 1 if the cell’s row_index
is lower than or equals to column_index
, else it leaves the cell blank.
Formula definition: =if(row_index<=column_index, 100+row_index1, "")
where row_index
and column_index
are argument placeholders defined for WATERFALL
.
Example: =MAKEARRAY(4,4,WATERFALL)
Result:
100 
100 
100 
100 
101 
101 
101 

102 
102 

103 
Use a named function as LAMBDA to find the count of employees
Use a named function
as a LAMBDA
function to find the count of employees that joined in a particular quarter in a particular year.
Example data:
A 
B 
C 
D 
E 
F 
G 
H 


1 
2020 
John,Adam,Stacy,Michael,Peter,Kimberly,Maurice,Steven 
Q1 
Q2 
Q3 
Q4 

2 
2021 
Nancy,Mark,Alice,Lily,Zack,Christina,Charles 
2020 

3 
2021 

4 

5 
Q1 
John,Adam,Nancy,Mark 

6 
Q2 
Stacy,Michael,Peter,Alice 

7 
Q3 
Kimberly,Lily,Zack,Christina 

8 
Q4 
Maurice,Steven,Charles 
In array A1:B2
, you’ll find the employees who joined in a particular year. In array A5:B8
, you’ll find the employees who joined in a particular quarter. We need to populate array E2:H3
with the count of employees who joined in a particular year and quarter.
Named function: FIND_COMMON_EMPLOYEES_COUNT
is a named function
which returns the count of common employees given a quarter number and year index.
Formula definition: =COUNT(ARRAYFORMULA(MATCH(SPLIT(INDEX(Sheet1!$B$5:$B$8, quarter_no),","), SPLIT(INDEX(Sheet1!$B$1:$B$2, year_index), ","), 0)))
, where year_index
and quarter_no
are argument placeholders defined for FIND_COMMON_EMPLOYEES_COUNT
, in that order.
Example: Input this formula in E2
: =MAKEARRAY(2, 4, FIND_COMMON_EMPLOYEES_COUNT)
Result:
A 
B 
C 
D 
E 
F 
G 
H 


1 
2020 
John,Adam,Stacy,Michael,Peter,Kimberly,Maurice,Steven 
Q1 
Q2 
Q3 
Q4 

2 
2021 
Nancy,Mark,Alice,Lily,Zack,Christina,Charles 
2020 
2 
3 
1 
2 

3 
2021 
2 
1 
3 
1 

4 

5 
Q1 
John,Adam,Nancy,Mark 

6 
Q2 
Stacy,Michael,Peter,Alice 

7 
Q3 
Kimberly,Lily,Zack,Christina 

8 
Q4 
Maurice,Steven,Charles 
Common Errors
The passed LAMBDA doesn't have exactly 2 name argumentsIf the LAMBDA
function doesn’t have 2 name
arguments, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 3 arguments, but got 2 arguments."
Example: =MAKEARRAY(2, 3, LAMBDA(current_value, current_value+1))
In this example, LAMBDA
was given only 1 name
argument when it needed 2.
If the last parameter of MAKEARRAY
wasn’t a LAMBDA
, this error occurs:
“Argument must be a LAMBDA.”
Example: =MAKEARRAY(2, 3, 3)
If the LAMBDA
passed to MAKEARRAY
was incorrect as to its name
arguments, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example: =MAKEARRAY(2,3, LAMBDA(C1, v, C1*v))
In this example, C1
is an invalid name
since it clashes with a range.
If the applied LAMBDA
on the row and column creates multiple values, this error occurs:
“Single value expected. Nested array results are not supported.”
Example: =MAKEARRAY(2,3, LAMBDA(i, j, {i, j} ))
Every application of LAMBDA
on the row_index
and column_index
must create a value which is a single value only and can't be another array.
Related functions
 LAMBDA function: This function lets you create and return a custom function with a set of
names
and aformula_expression
that uses them.  MAP function: This function maps each value in the given arrays to a new value.
 REDUCE function: This function reduces an array to an accumulated result.
 BYROW function: This function groups an array by rows.
 BYCOL function: This function groups an array by columns.
 SCAN function: This function scans an array and produces intermediate values.
 Create & use named functions: This function lets you create and store custom functions, similar to
LAMBDA
.