MAKEARRAY function
Quick answer
MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number.
- Syntax
=MAKEARRAY(rows, cols, lambda)- Most common use
=MAKEARRAY(A2,A2,LAMBDA(r,c,r*c))returns 1 (A 3 × 3 multiplication table)
Use it for multiplication and distance tables, simple calendars, or any grid whose values come from their position, all from one formula.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- An array of the size you asked for.
- Google Sheets
- Available in Google Sheets.
Syntax
=MAKEARRAY(rows, cols, lambda)
| Argument | Required? | What it is |
|---|---|---|
rows | Required | How many rows to make. |
cols | Required | How many columns to make. |
lambda | Required | A LAMBDA with two inputs, the row number and the column number, that returns the value for that position. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =MAKEARRAY(A2,A2,LAMBDA(r,c,r*c)) | 1spills 3 × 3 | A 3 × 3 multiplication table |
| C6 | =MAKEARRAY(2,3,LAMBDA(r,c,IF(r=c,1,0))) | 1spills 2 × 3 | 1 where the row and column numbers match |
| G2 | =SUM(MAKEARRAY(A2,A2,LAMBDA(r,c,r+c))) | 36 | Built and added up without showing the grid |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Size | ||||||
| 3 | 1 | 2 | 3 | 36 | ||
| 2 | 4 | 6 | ||||
| 3 | 6 | 9 | ||||
| 1 | 0 | 0 | ||||
| 0 | 1 | 0 |
Practice MAKEARRAY
Graded instantly, then re-checked on changed data.
Price grid
B5, use MAKEARRAY to build a grid with the number of rows in B1 and columns in B2, where each value is the row number times the column number times the unit price in B3.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | rows or cols is less than 1, or the LAMBDA doesn't have exactly two inputs. |
| #CALC! | The LAMBDA returns more than one value for a position. |
Tips
- =MAKEARRAY(12,1,LAMBDA(r,c,DATE(2026,r,1))) lists the first day of each month.
Questions
How do I make a multiplication table with one formula?
=MAKEARRAY(10,10,LAMBDA(r,c,r*c)).
What is the difference between MAKEARRAY and SEQUENCE?
SEQUENCE fills a grid with evenly spaced numbers. MAKEARRAY lets a LAMBDA decide each value from its row and column.
Updated Checked by running: every formula on this page was run by our formula engine on .