# MAKEARRAY function in Excel

> MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number.

Source: https://spreadsheetpractice.com/functions/makearray · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** An array of the size you asked for.
- **Google Sheets:** Available in Google Sheets.

## What it does

MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number. Use it for multiplication and distance tables, simple calendars, or any grid whose values come from their position, all from one formula.

## 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 was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | `=MAKEARRAY(A2,A2,LAMBDA(r,c,r*c))` | 1 (spills 3 × 3) | A 3 × 3 multiplication table |
| C6 | `=MAKEARRAY(2,3,LAMBDA(r,c,IF(r=c,1,0)))` | 1 (spills 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Size |  |  |  |  |  |  |
| 2 | 3 |  | 1 | 2 | 3 |  | 36 |
| 3 |  |  | 2 | 4 | 6 |  |  |
| 4 |  |  | 3 | 6 | 9 |  |  |
| 5 |  |  |  |  |  |  |  |
| 6 |  |  | 1 | 0 | 0 |  |  |
| 7 |  |  | 0 | 1 | 0 |  |  |

## 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.

## Related functions

- [SEQUENCE](https://spreadsheetpractice.com/functions/sequence): SEQUENCE generates a list or grid of numbers from one formula: 1 to 10, every fifth number, or a countdown.
- [LAMBDA](https://spreadsheetpractice.com/functions/lambda): LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)).
- [MAP](https://spreadsheetpractice.com/functions/map): MAP runs a LAMBDA on each value of an array, or on matching values from several arrays, and returns an array of the same size.

## 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.

## Practice

A graded MAKEARRAY exercise ("Price grid") is on [the MAKEARRAY page](https://spreadsheetpractice.com/functions/makearray). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.