Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use MAKEARRAY with LAMBDA in Excel and Google Sheets
Topic:LAMBDA (reusable formulas)Excel & Google Sheets
=MAKEARRAY(5, 5, LAMBDA(r, c, r * c))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Decide the dimensions of the grid you need — rows and columns are both hardcoded numbers or cell references.
- 2In A1, enter =MAKEARRAY(5, 5, LAMBDA(r, c, r * c)) to generate a 5×5 multiplication table. The formula spills into A1:E5.
- 3r is the 1-based row index (1 to 5); c is the 1-based column index (1 to 5). The body r * c computes the product for each cell.
- 4Change the body to any expression: r + c for an addition table, IF(r=c,1,0) for an identity matrix, or a nested formula referencing external ranges.
- 5Reference cell values from another range inside the LAMBDA by using INDEX(source, r, c) to pull from an existing dataset.
Tips
- MAKEARRAY is evaluated once per cell of the output grid, so keep the LAMBDA body efficient for large grids — avoid large array lookups inside it.
- Row and column indices start at 1, not 0. Adjust your formula accordingly if you need zero-based indexing.
- MAKEARRAY is only available in Microsoft 365 / Excel 2024+ and Google Sheets. It does not exist in earlier Excel versions.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I generate a date calendar grid with MAKEARRAY?
Use =MAKEARRAY(5, 7, LAMBDA(r, c, DATE(2024,1,1) + (r-1)*7 + (c-1))) to produce a 5-week × 7-day grid of dates starting on a given date.
Can MAKEARRAY reference values from another sheet?
Yes. Inside the LAMBDA body use INDEX(Sheet2!A1:Z50, r, c) to pull from an existing range. The LAMBDA has full access to any in-workbook reference.
What is the maximum size for MAKEARRAY?
There is no documented hard limit beyond the sheet grid itself (1,048,576 rows × 16,384 columns in Excel). Practically, very large grids will slow calculation significantly.
More on LAMBDA (reusable formulas)
See all →Reference
LAMBDA
Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.
How-toHow to write a recursive LAMBDA function
Build self-referencing recursive functions using LAMBDA with the Excel Name Manager or Google Sheets named functions — for tasks like factorial, Fibonacci, or tree traversal.
How-toHow to use LAMBDA with MAP across a range
Use MAP with a LAMBDA to apply a custom formula to every cell in a range in Excel 365 and Google Sheets, returning a transformed array without helper columns.
How-toHow to use LAMBDA with REDUCE for cumulative math
Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.
How-toHow to use LAMBDA with SCAN for a running balance
Use SCAN with a LAMBDA to compute a running balance or cumulative total in Excel 365 and Google Sheets, returning one result per row without helper columns.
How-toHow to use BYROW and BYCOL to apply a LAMBDA per row or column
BYROW and BYCOL apply a LAMBDA to each row or column of a range in Excel 365 and Google Sheets, returning a per-row or per-column result without helper rows or columns.