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.
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .