How to Use MAKEARRAY Formula in Google Sheets
In this article, you will learn the MAKEARRAY function and how to use it in Google Sheets. This function is meant to be used with the LAMBDA function. The MAKEARRAY formula applies the LAMBDA function to the range of cells you specify. The uniqueness of the MAKEARRAY formula is that the LAMBDA formula uses rows’ and columns’ numbers, which are not Google Sheets’ ones but those of the array you want to make as input values. If you are unfamiliar with the LAMBDA function, check this article to learn how to use the formula.
How to insert the MAKEARRAY formula in Google Sheets
- Type “=MAKEARRAY” or go to “Insert” → “Function” → “Array” → “MAKEARRAY”.
- Define the array size you want to make by inputting “rows” and “columns”.
- Enter a LAMBDA function with placeholders and logic.
- Press the “Enter” key.
What is the MAKEARRAY function in Google Sheets?
The MAKEARRY function is beneficial when you need to create a table in which you use either of a cell’s row and column numbers in the table as input values for some calculation or when you want to spread something like texts or symbols in a specified range.
The generic formula is as follows:
Rows: This figure defines the number of rows of the array you create.
Columns: This figure defines the number of columns of the array you create.
Lambda: Input the LAMBDA function such as “LAMBDA(RowNumber, ColumnNumber, RowNumber + ColumnNumber)”. You can just input a text, a number, a date, or a formula without referring to the placeholders defined. Again, in this MAKEARRAY formula, the LAMBDA runs a process in each cell in the array you create, and the LAMBDA uses each cell’s row and column numbers (not Google ones’) as input values. So, you need to define two placeholders in the LAMBDA, the first one corresponds to a row number, and the second one resolves to a column number. Also, ensure you secure enough space for this formula because it spreads the results to the specified array. You can use your Named Functions here.
As a simple example, assume you need to create a 5x5 Times table in Google Sheets. Use the MAKEARRAY formula instead of typing each calculation and answer in the array one by one. Once you type the MAKEARRAY formula in a cell, it automatically spreads the results in the 5x5 array. Also, this example explains how the formula works visually - you can see multiplication factors in a cell correspond to the cell’s row and column numbers.
The definitions of each argument of the MAKEARRAY formula in this example are as follows.
Rows: 5
Columns: 5
Lambda: LAMBDA(RowNumber,ColumnNUmber,RowNumber&" x "&ColumnNumber&" = "&RowNumber*ColumnNumber)
If you are interested in how to combine two or more values, check this article.
Look at the following screenshot. This is another example of the MAKEARRAY function, in which we use text as input values. We defined the size of the array as 5x5 and gave the LAMBDA an order to put “LiveFLow” in each cell in the specified range.
How do I apply an ARRAYFORMULA function in Google Sheets?
Read this article: ARRAYFORMULA Function in Google Sheets: Explained to learn how to use the ARRAYFORMULA function in Google Sheets.
How do I make a formula auto-populate in sheets?
Go to this page: Autofill Function in Google Sheets: Explained to learn how to use the Autofill function in Google Sheets.
