Logical

MAKEARRAY Function in Excel

Returns a calculated array of specified row and column size by applying a LAMBDA.

Syntax

  • =MAKEARRAY(rows, cols, lambda)

Arguments

  • rows (required): Number of rows
  • cols (required): Number of columns
  • lambda (required): LAMBDA(row, col) to generate values

Examples

  • =MAKEARRAY(3, 3, LAMBDA(r,c, r*c)) - Multiplication table - Result: 3x3 array

Excel MAKEARRAY function documentation

  • MAKEARRAY creates a calculated array of specified rows and columns via LAMBDA(row, col).
  • Syntax: =MAKEARRAY(rows, cols, LAMBDA(r, c, calculation)). Microsoft 365.
  • Build matrices, multiplication tables, and coordinate grids without manual fill.
  • Related: [SEQUENCE](/functions/sequence/), [MAP](/functions/map/), [BYROW](/functions/byrow/).

MAKEARRAY syntax

  • rows, cols: dimensions of output spill grid.
  • LAMBDA(r, c, ...) — r and c are 1-based indices at each cell.
  • Can reference external data inside lambda using careful offset logic.
  • Large dimensions slow recalc — cap grid size in models.

Step-by-step: multiplication table 10x10

  • Step 1 — =MAKEARRAY(10, 10, LAMBDA(r, c, r*c)).
  • Step 2 — Spills 10x10 — ensure free block.
  • Step 3 — Format as table for print.
  • Step 4 — Headers: use SEQUENCE for row/col labels separately.
  • Step 5 — Compare legacy =ROW()*COLUMN() fill patterns.

MAKEARRAY vs SEQUENCE vs manual grid

  • MAKEARRAY — 2D formula-defined cells.
  • SEQUENCE — often 1D or simple increments.
  • Manual fill — static.
  • Logical/modern Excel: [/functions/lambda/](/functions/lambda/).

Worked examples to copy

  • Times table: =MAKEARRAY(12,12,LAMBDA(r,c,r*c)).
  • Identity-ish: =MAKEARRAY(n,n,LAMBDA(r,c, IF(r=c,1,0))).
  • Coordinates: =MAKEARRAY(rows,cols,LAMBDA(r,c, r&","&c)).
  • Random grid: combine with RAND in lambda.

People also ask

  • MAKEARRAY Excel 2019? — No; 365.
  • MAKEARRAY #SPILL!? — Destination blocked or too large.
  • MAKEARRAY vs MMULT? — MAKEARRAY builds; MMULT multiplies existing matrices.
  • MAKEARRAY volatile in lambda? — Depends on inner formula.

Common errors

  • Requires Excel 365
  • LAMBDA needs row and col parameters

Use cases

  • Generate arrays
  • Custom matrices
  • Dynamic tables

Frequently asked questions

  • How is MAKEARRAY different from SEQUENCE? SEQUENCE generates sequential numbers with fixed patterns. MAKEARRAY lets you define any formula based on row and column position. Use MAKEARRAY for complex patterns like =MAKEARRAY(5, 5, LAMBDA(r,c, IF(r=c, 1, 0))) for an identity matrix.
  • How do I create a distance matrix with MAKEARRAY? Use =MAKEARRAY(n, n, LAMBDA(r,c, ABS(r-c))) for simple distance, or reference external data: =MAKEARRAY(5, 5, LAMBDA(r,c, SQRT((INDEX(X,r)-INDEX(X,c))^2 + (INDEX(Y,r)-INDEX(Y,c))^2))) for Euclidean distances.
  • Can MAKEARRAY reference cells outside the array? Yes! The LAMBDA can reference any cells. =MAKEARRAY(ROWS(data), COLS(data), LAMBDA(r,c, INDEX(data,r,c)*1.1)) creates a copy of data with 10% increase. Combine with INDEX, OFFSET, or direct references.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MAKEARRAY

  • Generate arrays — common Logical scenario for MAKEARRAY.
  • Custom matrices — common Logical scenario for MAKEARRAY.
  • Dynamic tables — common Logical scenario for MAKEARRAY.

MAKEARRAY in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • MAKEARRAY syntax: =MAKEARRAY(rows, cols, lambda)
  • rows (required): Number of rows
  • cols (required): Number of columns
  • lambda (required): LAMBDA(row, col) to generate values
  • Confirm MAKEARRAY arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MAKEARRAY arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • LAMBDA (/functions/lambda/): Creates custom, reusable functions that can be called by a friendly name.
  • SEQUENCE (/functions/sequence/): Generates a list of sequential numbers in an array.
  • MAP (/functions/map/): Returns an array formed by mapping each value in the array(s) to a new value using a LAMBDA.

Errors to watch for

  • Requires Excel 365 — review causes on linked error pages in the directory.
  • LAMBDA needs row and col parameters — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MAKEARRAY on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MAKEARRAY worked examples to copy

  • =MAKEARRAY(3, 3, LAMBDA(r,c, r*c)) — Multiplication table. Expected result: 3x3 array.

MAKEARRAY reference summary for crawlers and offline review

  • MAKEARRAY belongs to the Logical category in Excel. Returns a calculated array of specified row and column size by applying a LAMBDA.
  • Full syntax: =MAKEARRAY(rows, cols, lambda). Open /functions/makearray/ for parameters, FAQs, and related pages.
  • Common mistakes: Requires Excel 365; LAMBDA needs row and col parameters
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.