MAKEARRAY Function

Excel 365

The Excel MAKEARRAY function conjures an array out of thin air: you say how many rows and columns you want, and a LAMBDA computes each cell from its row index and column index. Where MAP and BYROW transform data you already have, MAKEARRAY builds grids from pure index math — multiplication tables, checkerboards, schedule grids, simulated data.

Quick answer: a 4×4 multiplication table from a single formula:

=MAKEARRAY(4, 4, LAMBDA(r, c, r*c))

For each of the 16 cells, the LAMBDA receives that cell’s row number r (1–4) and column number c (1–4) and returns r*c.

Syntax

=MAKEARRAY(rows, cols, lambda(row_index, col_index, body))

Argument		Description
`rows`	Required	Number of rows in the output. Must be at least 1.
`cols`	Required	Number of columns in the output. Must be at least 1.
`lambda`	Required	A two-parameter LAMBDA: the current cell’s row index, then its column index (both starting at 1). The body returns that cell’s value.

Available in: Excel for Microsoft 365 and Excel for the web only. Older versions show #NAME?. The result spills as a rows × cols dynamic array.

Build a grid from index math

One formula, no source data. Each cell is computed from its coordinates:

=MAKEARRAY(4, 4, LAMBDA(r, c, r*c)) // spills the table below

	A	B	C	D
1	1	2	3	4
2	2	4	6	8
3	3	6	9	12
4	4	8	12	16

Anything you can express in terms of “which row am I, which column am I” works:

=MAKEARRAY(8, 8, LAMBDA(r, c, IF(ISEVEN(r+c), "■", ""))) // a checkerboard

=MAKEARRAY(5, 3, LAMBDA(r, c, (r-1)*3 + c)) // numbers 1-15 snaking left to right

Try it: size a times table live

Live demo

Drag the sliders — MAKEARRAY regenerates the multiplication table at the new size.

rows: 5

cols: 5

Practical grids: calendars, lookups, simulations

Real work rarely needs a times table, but it constantly needs structured grids. A month-view calendar skeleton — 5 weeks × 7 days, numbered from a chosen weekday offset:

=MAKEARRAY(5, 7, LAMBDA(w, d, LET(n, (w-1)*7 + d - 3, IF(AND(n>=1, n<=30), n, "")))) // a 30-day month starting on a Thursday

Index math also reshapes data: pull a flat list into a 2-D grid by computing which list item belongs in each cell:

=MAKEARRAY(4, 3, LAMBDA(r, c, INDEX(A1:A12, (r-1)*3 + c))) // wrap a 12-item column into 4 rows of 3

And quick simulated data for testing:

=MAKEARRAY(10, 4, LAMBDA(r, c, RANDBETWEEN(10, 99))) // a 10x4 block of random two-digit numbers

Simpler tool first: for a plain run of numbers, SEQUENCE does it with less ceremony. MAKEARRAY earns its keep when each cell needs logic, not just a count. To process existing data instead, see MAP, BYROW, and REDUCE.

Errors & common pitfalls

#CALC! — wrong parameter count in the LAMBDA. MAKEARRAY always passes two values — row index, then column index — so the LAMBDA must declare exactly two parameters even if the body only uses one: LAMBDA(r, c, r^2).

#VALUE! — rows or cols isn’t a positive number. Zero, negative, or text dimensions fail. Decimals get truncated.

#NAME? — older Excel. MAKEARRAY exists only in Excel for Microsoft 365 and Excel for the web.

Pitfall: the indexes are array-relative, not sheet rows. r and c always start at 1 in the array’s top-left corner, no matter where the formula lives on the sheet. Don’t mix them up with ROW() and COLUMN(), which return sheet coordinates.

Pitfall: #SPILL! and performance. The output needs a clear rows × cols landing zone, and every cell runs the LAMBDA once — a 1,000×1,000 MAKEARRAY is a million evaluations. Keep grids sized to what you’ll actually read.

Practice workbook

📊

Download the free MAKEARRAY practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What does the Excel MAKEARRAY function do?

MAKEARRAY generates a new array of a size you specify, computing every cell with a LAMBDA that receives that cell's row index and column index (both starting at 1). It builds grids from logic instead of transforming existing data.

What's the difference between MAKEARRAY and SEQUENCE?

SEQUENCE only counts — evenly spaced numbers in a grid. MAKEARRAY runs arbitrary logic per cell: conditions, text, lookups, anything expressible from the row and column indexes. If SEQUENCE can do it, prefer SEQUENCE; it's simpler and faster.

What's the difference between MAKEARRAY and MAP?

MAP transforms an existing array element by element; MAKEARRAY creates a new array from nothing but dimensions and index math. No source data, MAKEARRAY; transforming source data, MAP.

Why does MAKEARRAY return #CALC!?

Most often the LAMBDA doesn't declare exactly two parameters. MAKEARRAY always hands over the row index and the column index — write LAMBDA(r, c, body) even when the body ignores one of them.

Can I make a multiplication table in Excel with one formula?

Yes: =MAKEARRAY(10, 10, LAMBDA(r, c, r*c)) spills a complete 10×10 times table. Change the two dimension arguments for any other size.

Which Excel versions support MAKEARRAY?

Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME?.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: LAMBDA · SEQUENCE · MAP · BYROW · BYCOL · REDUCE · INDEX