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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.