Google Sheets Formulas
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.
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.
Read this article to learn how to use the ARRAY formula in Google Sheets.
Go to this page to learn how to use the Autofill function in Google Sheets.
Check the articles below to learn about other LAMBDA helper functions such as MAP, SCAN, REDUCE, BYCOL and BYROW.
How to Use MAP Function in Google Sheets
How to Use SCAN Function in Google Sheets
How to Use REDUCE Function in Google Sheets
How to Use BYCOL Function in Google Sheets
How to Use BYROW Function in Google Sheets
Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access more than fifty templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇
