MAKEARRAY function

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: A LAMBDA that’s applied to create the array.
  • Syntax: LAMBDA(name1, name2, formula_expression)
    • Learn more about LAMBDA functions.
  • Requirements:
    • The LAMBDA must have exactly 2 name arguments along with a formula_expression which uses those names. When applying the LAMBDA, name1 resolves to the current row_index and name2 resolves to the current column_index. 

Notes

• The passed LAMBDA function should accept exactly 2 name arguments, otherwise an #N/A error is returned. These correspond to row_index and column_index, in order. These are explained as:

  • name1: Resolves to the current row_index for which value is created.
  • name2: Resolves to the current column_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 and column_index start from 1.
• A named function can be passed for the LAMBDA parameter and behaves like a LAMBDA function in this case. Learn more about named functions.
  • The named function must follow the LAMBDA syntax for MAKEARRAY with exactly 2 argument placeholders defined for it.
  • The named function shouldn't be followed by parenthesis.

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.

Make a Copy

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.

Make a Copy

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 function: This function lets you create and return a custom function with a set of names and a formula_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.