The MAKEARRAY function in Google Sheets returns a calculated array of specified dimensions (defined by row and column size) by applying a LAMBDA function. This is especially useful when you want to dynamically generate an array where each cell is based on its row and column positions.

In this guide, we’ll first explore how to use the MAKEARRAY function with LAMBDA, and then look at how it fits into more practical, real-world use cases.

MAKEARRAY Function Syntax and Arguments in Google Sheets

Syntax:

MAKEARRAY(rows, columns, LAMBDA)

Arguments:

• rows – The number of rows in the resulting array.
• columns – The number of columns in the resulting array.
• LAMBDA – A lambda function that accepts two parameters: the row index and the column index.

Note:

When using LAMBDA in a standalone formula, you need to include a function call like this:

=LAMBDA(row, col, row * col)(3, 2)

But in MAKEARRAY, this part is omitted, as MAKEARRAY handles the input dynamically.

Basic Examples of the MAKEARRAY Function

Let’s look at some basic examples to understand how MAKEARRAY works:

Example 1:

=MAKEARRAY(3, 2, LAMBDA(row, column, row))

Returns: Each row number repeated across 2 columns:

Example 2:

=MAKEARRAY(3, 2, LAMBDA(row, column, column))

Returns: Each column number repeated down 3 rows:

Example 3:

=MAKEARRAY(3, 3, LAMBDA(row, column, row * column))

Returns: A multiplication table:

Each cell is the product of its row and column index.

Example 4:

=MAKEARRAY(2, 5, LAMBDA(row, column, "🌻"))

Returns: A 2×5 array filled with sunflowers:

Real-life Use of the MAKEARRAY Function in Google Sheets

Example 1: Generating a Calendar Grid

You can use MAKEARRAY to create a dynamic calendar layout.

1. In cell A1, enter the first day of the month (e.g., 2024-08-01).
2. In cell B1, enter the following to display weekday names:

=HSTACK("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

3. In cell B2, enter this formula to generate a 6×7 calendar grid:

=MAKEARRAY(6, 7, LAMBDA(r, c,
  LET(
    dt, A1 - WEEKDAY(A1) + ((r - 1) * 7 + c),
    IF(MONTH(dt) <> MONTH(A1),, dt)
  )
))

How It Works:

• Calculates the offset from the start of the month to the beginning of the week: It adjusts the given date (A1) backward to the nearest Sunday (or start of the week), ensuring the calendar grid starts from the right weekday.
• Fills in a 6×7 grid representing weeks and days: The formula loops through 6 rows and 7 columns to cover all days, even in months that span across six weeks.
• Leaves blanks for days not in the current month: If the calculated date dt falls outside the current month (i.e., MONTH(dt) <> MONTH(A1)), the formula returns a blank cell instead of a date.

Note: You can use a non-lambda function to get the same result. Here it is:

=ArrayFormula(
   TO_DATE(
      LET(
         dt, A1 - WEEKDAY(A1)+SEQUENCE(6, 7, 1), 
         IF(MONTH(dt)<>MONTH(A1),,dt)
      )
)
)

Just like SEQUENCE(6, 7, 1) generates a 6×7 grid of values, the MAKEARRAY function in the previous example does the same—but with more flexibility to customize how each cell is calculated using LAMBDA.

Example 2: Expanding MAP Function with MAKEARRAY

Let’s say you have a product price table across regions and want to identify the first region with the lowest price for each product.

Sample Data:

Formula using MAKEARRAY:

=MAKEARRAY(ROWS(B2:E5), 1, LAMBDA(r, c,
  MATCH(MIN(INDEX(B2:E5, r, )), INDEX(B2:E5, r, ), 0))
)

Explanation of the MAKEARRAY with MATCH Example

• INDEX(B2:E5, r, ) grabs the price row for the r-th product
• MIN(...) gets the lowest price for that product
• MATCH(..., ..., 0) finds the position of the first match
• You get a vertical list of the column positions of the lowest price for each product

Alternative Using BYROW:

=BYROW(B2:E5, LAMBDA(r, MATCH(MIN(r), r, 0)))

This version does the same thing using BYROW, which is more concise, but we used MAKEARRAY to demonstrate how it can be used similarly.

Conclusion

The MAKEARRAY function in Google Sheets is a powerful tool for generating dynamic, calculated arrays where each cell depends on its row and column position. Whether you’re building visual layouts like calendars, simulating logic across a matrix, or creating flexible array formulas, MAKEARRAY offers a clean and efficient solution.

When paired with LAMBDA, it opens up highly customizable logic-driven arrays that scale across your spreadsheet. While functions like BYROW or MAP may sometimes be simpler, MAKEARRAY shines when you want full control over both row and column behavior.

Let us know how you use MAKEARRAY in your workflows!