How to Use MAKEARRAY Function in Google Sheets

This guide will explain how to use the MAKEARRAY function in Google Sheets.

The MAKEARRAY function allows you to quickly generate an array of values using a single formula. This allows users to easily update and manipulate a calculated array by modifying a single formula rather than having to update a range of formulas.

For example, suppose we are tasked to generate a 5×5 grid of random integers from 1 to 100. Instead of copying the formula =RANDBETWEEN(1,100) 25 times in your sheet, we can use MAKEARRAY to define a calculated array with our desired dimensions.

In this guide, we will provide a step-by-step tutorial on how to start using the MAKEARRAY function.

The Anatomy of the MAKEARRAY Function

The syntax of the MAKEARRAY function is as follows:

=MAKEARRAY(rows, columns, LAMBDA(row, col, calculation))

Let’s look at each argument to understand how to use the MAKEARRAY function.

• rows refers to the number of rows in the array to be created. This number must be larger than zero.
• columns refers to the number of desired columns in the array. This value must also be larger than 0.
• LAMBDA(row, col, calculation) defines a LAMBDA function that will be used to generate the values within the array. The name arguments row_index and column_index will be used as input for row and col. The calculation argument will then define a formula to generate the value of each cell based on that cell’s row and column.
• Do note that row_index and column_index always start from 1.

A Real Example of the MAKEARRAY Function in Google Sheets

Let’s explore a simple example where we could use the MAKEARRAY function to improve our spreadsheet.

In the table above, we used the AVERAGE function to find the mean of a range of variables in the cells A1:C10. Each cell in the range uses the formula RANDBETWEEN(1,100) to generate a random value.

We may need to increase the number of rows or columns of our range of randomly generated values. We may also have to update the lower and upper limits of our RANDBETWEEN function. 

Instead of relying on multiple formulas, we can use the MAKEARRAY function instead to generate the entire array. 

We’ve used the following formula to generate our array:

=MAKEARRAY(10,3,LAMBDA(row_index,col_index,RANDBETWEEN(1,100)))

The first two arguments of MAKEARRAY determine the number of rows and columns to generate. In our formula, we’ve set it up to create an array with 10 rows and 3 columns.

The third argument must be a LAMBDA function. The LAMBDA function allows us to define a custom function with placeholder inputs. In the context of the MAKEARRAY function, the LAMBDA function takes in the row index and column index of each cell in the array and is able to use these values when calculating the output.

The LAMBDA function in our MAKEARRAY function simply outputs a random integer using RANDBETWEEN without using the row_index and column_index values. 

The RANDBETWEEN function takes a lower limit and an upper limit as arguments and outputs a random integer between these two limits. If we set the arguments to 1 and 100, our functions will output a random number between 1 and 100.

Let’s take a look at another example where the MAKEARRAY function uses the row_index and column_index values to help calculate the value of each cell.

In the example above, we were able to create a basic multiplication table using the MAKEARRAY function. To create this output, we used the following formula:

=MAKEARRAY(10,10,LAMBDA(row_index,column_index,row_index*column_index))

The formula above defines a calculated array with 10 rows and 10 columns where the value in each cell is determined by the following function:

LAMBDA(row_index,column_index,row_index*column_index)

This means that the generated value is equal to the product of the current cell’s row index and column index. For example, cell H7 has a value of 56 since it lies on the seventh row and eighth column.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to start using the MAKEARRAY function in Google Sheets.

How to Use MAKEARRAY Function in Google Sheets

1. Select the cell where you want to place the MAKEARRAY function.
   Ensure there are enough empty cells around this cell to prevent overlapping data.
   
2. Write down the MAKEARRAY function and input the number of desired rows and columns, respectively. In this example, we’ll create a 5×5 size array.
   
3. Next, add the formula LAMBDA(row_index,column_index, as the third argument of the MAKEARRAY function.
   
4. Complete the LAMBDA function by adding the calculation you want to make for each cell.
   In our example, we’ll use the formula RANDBETWEEN(1,100) to generate a random integer between 1 and 100.
   
5. Hit the Enter key to evaluate the MAKEARRAY function.
   

You should now be comfortable using the MAKEARRAY function to generate a calculated array of a specific size. Another way we can simplify formulas in your spreadsheet is through the LET function.

FAQs

1. Why does my MAKEARRAY function return an error?
   If you encounter a #N/A error, there may be issues with your LAMBDA argument. Ensure that the LAMBDA function used contains two name arguments and a single calculation argument. If the #N/A error includes the message “Argument must be a lambda” then you must convert your third argument into a LAMBDA function.

If you want to learn more, be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for spreadsheet users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

Subscribe