How can I use the MAKEARRAY function in Google Sheets?

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:

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:

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:

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:

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:

Common Errors

The passed LAMBDA doesn’t have exactly 2 name arguments

The last parameter of MAKEARRAY wasn’t a LAMBDA

The LAMBDA passed to MAKEARRAY was incorrect

The application of LAMBDA on a row and column index creates multiple values

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.