Skip to main content
SheetHub Docs
Navigation Menu
SheetHubDOCSSpreadsheet Knowledge Base
About
Formulas & Functions•7 min read

Excel MAKEARRAY Function: Generate Arrays with LAMBDA

Generate custom arrays with Excel MAKEARRAY and LAMBDA. Build multiplication tables, random grids, and dummy data.

SheetHub••7 min
Did you know that manually creating a 9×9 multiplication table requires 81 separate formulas? MAKEARRAY reduces it to one.

What is MAKEARRAY?

MAKEARRAY is one of the functions that only exists because LAMBDA does. It creates an array of a given size and calls your LAMBDA once for every cell, passing the row number and column number as parameters: 
=MAKEARRAY(rows, columns, LAMBDA(r, c, expression))
The three arguments are:
ArgumentWhat It Does
rowsNumber of rows in the result. Must be greater than zero.
columnsNumber of columns in the result. Must be greater than zero.
LAMBDA(r, c, ...)Called for each cell. r is the row index, c is the column index, and the expression returns the value for that cell.
 Availability: MAKEARRAY requires Excel for Microsoft 365 (Windows, Mac, and web). It is not available in Excel 2021, 2019, or earlier. Older versions return #NAME? because both MAKEARRAY and LAMBDA are missing. Mental model: you are not writing a formula for one cell and copying it. You are describing the entire grid in one expression, and Excel fills in every cell by varying r and c.

Example 1: A multiplication table in one formula

The classic MAKEARRAY demo is a 9×9 multiplication table. This single formula produces all 81 values: 
=MAKEARRAY(9, 9, LAMBDA(r, c, r*c))
The formula builds 9 rows and 9 columns. Each cell multiplies its row number by its column number, so cell (3, 4) gets 3*4 = 12 and cell (7, 2) gets 14. Change 9 to 12 and the table grows with one edit instead of 144 copied formulas. To add dynamic headers, reference two cells that hold the table size: 
=MAKEARRAY(B1, B2, LAMBDA(r, c, r*c))
Now the grid resizes itself whenever you update B1 or B2.

Example 2: A random grid

A 5×5 grid of random numbers between 1 and 100 can represent a dice-roll simulator, a bingo card, or test data. The row and column setup stays the same; only the expression changes: 
=MAKEARRAY(5, 5, LAMBDA(r, c, RANDBETWEEN(1, 100)))
RANDBETWEEN is volatile, so the whole grid recalculates every time the sheet changes. If you need a stable snapshot, copy the result and paste it as values with Ctrl + Shift + V (Windows) or Cmd + Shift + V (Mac).

Example 3: Dummy data with INDEX

MAKEARRAY is useful for realistic sample data. To generate 10 rows of names cycling through a small list, combine INDEX with MOD: 
=MAKEARRAY(10, 3, LAMBDA(r, c, INDEX({"Alice","Bob","Cara"}, MOD(r-1,3)+1)))
The MOD(r-1,3)+1 part cycles through 1, 2, 3, 1, 2, 3... so each row picks the next name and wraps around. Because c is unused here, every column repeats the same sequence. This is a quick way to build a 10×3 block of names for testing. Swap the array constant for product names or regions to shape the dummy data however you like.

Combining MAKEARRAY with XLOOKUP

You can call any function inside the LAMBDA, including XLOOKUP. A common pattern is building a lookup result for each row index: 
=MAKEARRAY(5, 1, LAMBDA(r, c, XLOOKUP(r, $A$2:$A$10, $B$2:$B$10)))
This returns the first five matching values from column B, one per row. Two details matter here:
  1. Anchor the lookup range. $A$2:$A$10 and $B$2:$B$10 use absolute references. Relative references shift inside MAKEARRAY and produce wrong or missing matches.
  2. The LAMBDA must return a scalar. XLOOKUP returns a single value, so it works. If your LAMBDA returns an array, such as a whole row from INDEX, MAKEARRAY throws #VALUE!.

Real use cases

ScenarioFormula
Multiplication table 9×9=MAKEARRAY(9, 9, LAMBDA(r, c, r*c))
Random grid 5×5=MAKEARRAY(5, 5, LAMBDA(r, c, RANDBETWEEN(1,100)))
Dummy data, 10 rows of cycling names=MAKEARRAY(10, 3, LAMBDA(r, c, INDEX({"Alice","Bob","Cara"}, MOD(r-1,3)+1)))
Identity matrix=MAKEARRAY(5, 5, LAMBDA(r, c, IF(r=c, 1, 0)))
Lookup per row=MAKEARRAY(5, 1, LAMBDA(r, c, XLOOKUP(r, $A$2:$A$10, $B$2:$B$10)))
 In every case, the result is a dynamic array that spills like FILTER or SEQUENCE. The dimensions stay fixed, while the values recalculate whenever the inputs change.

Error handling and limitations

  • #VALUE!: LAMBDA returns an array. The expression must return one scalar per cell. If you need a sub-array, wrap it with INDEX or CHOOSE so the LAMBDA extracts a single value.
  • #VALUE!: invalid dimensions. Rows or columns set to 0, negative, or non-numeric values throw #VALUE!. Guard dynamic sizes with MAX: 
    =MAKEARRAY(MAX(1, B1), MAX(1, B2), LAMBDA(r, c, r*c))
  • #NAME?: older Excel. MAKEARRAY and LAMBDA are Microsoft 365 only. Workarounds: drag a regular formula, or generate data with Power Query.
  • Performance on large grids. Every cell runs the LAMBDA, so a 100×100 grid means 10,000 calculations. Keep grids reasonable and avoid heavy volatile functions inside the LAMBDA.
  • Volatile functions recalculate constantly. RANDBETWEEN and RAND inside MAKEARRAY recompute on every sheet change. Paste as values when you need stable output.

MAKEARRAY vs alternatives

MethodProsCons
MAKEARRAYOne formula, fully dynamic, custom logic per cellMicrosoft 365 only, requires LAMBDA
Dragged formulaWorks in every Excel versionManual, easy to break, static
Power QueryHandles large generated datasetsSetup and refresh overhead
VBA / macrosMaximum controlRequires macro-enabled files
 If you only need sequential numbers such as 1, 2, 3, 4..., SEQUENCE is simpler and lighter. Use MAKEARRAY when each cell needs different logic, like the multiplication table or the random grid.

Pro tips

  • Define your source lists with LET before passing them into the LAMBDA. A named list keeps the LAMBDA expression readable instead of nesting a long array constant inside INDEX.
  • Combine with SEQUENCE when you want to transform row and column positions, such as offsetting or stepping values.
  • Test the LAMBDA in one cell first. Write =LAMBDA(r, c, r*c)(3, 4) in a single cell to confirm the expression returns the expected scalar before wrapping it in MAKEARRAY.

Common mistakes to avoid

  1. Returning an array from the LAMBDA. Each call must produce one value, or you get #VALUE!.
  2. Passing zero or negative dimensions. Rows and columns must be positive integers.
  3. Using MAKEARRAY for simple sequential lists. SEQUENCE does that with less overhead.
  4. Forgetting absolute references in lookups inside the LAMBDA, which silently shifts the results.

FAQ

Does MAKEARRAY require LAMBDA? Yes. The third argument is a LAMBDA that calculates each cell. Can I generate random text? Yes. Combine INDEX with RANDBETWEEN, or pick from a list with MOD(r-1, n)+1 as in the dummy data example. Why do I get #VALUE!? Either the LAMBDA returned an array instead of a scalar, or the rows/columns arguments were below 1 or non-numeric. Does Google Sheets have MAKEARRAY? No. Sheets uses SEQUENCE, ARRAYFORMULA, or MAP for similar jobs.

AI prompt callout

After you understand the pattern, you can describe the grid you need to an AI assistant (ChatGPT, Gemini, or Claude) and ask for a formula:
AI prompt template
I have a pricing matrix with 8 products and 5 regions. I need a formula that returns an 8×5 grid where each cell is the product's base price times a region multiplier. Region multipliers are in row 1, base prices are in column A. Use MAKEARRAY and LAMBDA, with absolute references to the multiplier and price ranges.
Adjust the ranges and multipliers to match your data.

SheetHub's take

MAKEARRAY lets you describe a grid with its row count, column count, and the LAMBDA that calculates each cell. It works well for multiplication tables, test grids, and lookup results that would otherwise require copied formulas. Start with the 9×9 table, then try INDEX or XLOOKUP inside the LAMBDA. Once you see how r and c change from cell to cell, the function becomes much easier to use.
Article Topics
#excel#formulas#makearray#lambda#advanced

Recommended Next Reading

All Articles

Share this tutorial

Discussion & Community

Share questions, tips, or edge-cases about this spreadsheet formula.

Recommended Next Reading

All Articles