Practice free

MAKEARRAY function

MAKEARRAY builds an array of a size you choose by running a custom calculation for every row and column position, such as a multiplication table. The calculation receives positions starting at 1.

Example

Read it as a sentence: build a 3 by 3 grid where each cell is its row number times its column number, then add up all the numbers, which gives 36.

Practice this in the app

Syntax

MAKEARRAY(rows, columns, lambda)

rows (required)
Number of rows to build.
columns (required)
Number of columns to build.
lambda (required)
A LAMBDA(row, column) that returns each cell's value.

Returns: An array of the given size filled by the lambda.

One MAKEARRAY formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=MAKEARRAY(2,3,LAMBDA(r,c,r*c)) 1 Times table
=MAKEARRAY("2",2,LAMBDA(r,c,r)) 1 Text rows
=MAKEARRAY(0,1,LAMBDA(r,c,r)) #VALUE! Zero rows
=MAKEARRAY(2,2,5) #VALUE! Not a lambda
=MAKEARRAY(1.9,2,LAMBDA(r,c,c)) #VALUE! Fractional rows
=MAKEARRAY(2,2,LAMBDA(r,r)) #VALUE! Lambda with one parameter
=MAKEARRAY(1,1,LAMBDA(r,c,1/0)) #DIV/0! Error element

Watch out for

  • The lambda receives positions starting at 1.
  • Large sizes are slow; SEQUENCE is simpler for plain number grids.

Where it works

Excel
MAKEARRAY arrived in Excel 2024. Older versions do not have it. Dynamic-array LAMBDA family; in 365 and Excel for the web, and Excel 2024. Check older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .