Arrow left
Back to product guides
Google Sheets Formulas

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

  1. Type “=MAKEARRAY” or go to “Insert”“Function”“Array” → “MAKEARRAY”.
  2. Define the array size you want to make by inputting “rows” and “columns”.
  3. Enter a LAMBDA function with placeholders and logic.
  4. Press the “Enter” key.
How to insert the MAKEARRAY formula in Google Sheets

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:

=MAKEARRAY(rows, columns, lambda)

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.

How to use the MAKEARRAY formula with a practical example

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 the MAKEARRAY formula works with text input in Google Sheets

How do I apply an ARRAY formula in Google Sheets?

Read this article to learn how to use the ARRAY formula in Google Sheets.

How do I make a formula auto-populate in sheets?

Go to this page to learn how to use the Autofill function in Google Sheets.

Other LAMBDA support functions

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

Analyze your live financial data in a snap

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.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at:
Check Icon
Call us at +1 (415) 650-1711