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_index-1, "")
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 arguments
If 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.
The last parameter of MAKEARRAY wasn’t a LAMBDA
If the last parameter of MAKEARRAY
wasn’t a LAMBDA
, this error occurs:
“Argument must be a LAMBDA.”
Example: =MAKEARRAY(2, 3, 3)
The LAMBDA passed to MAKEARRAY was incorrect
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.
The application of LAMBDA on a row and column index creates multiple values
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: This function lets you create and return a custom function with a set of
names
and aformula_expression
that uses them. - MAP: This function maps each value in the given arrays to a new value.
- REDUCE: This function reduces an array to an accumulated result.
- BYROW: This function groups an array by rows.
- BYCOL: This function groups an array by columns.
- SCAN: 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
.