Quick Navigation

MAKEARRAY Function

Excel 365, Excel 2021

Summary

The MAKEARRAY function dynamically generates an array of specified dimensions by executing a LAMBDA function for each cell position. This powerful function enables the creation of complex calculated arrays without manual formula replication across ranges.

Syntax

=MAKEARRAY(rows, cols, lambda(row, col))

Parameters

Parameter	Type	Required	Description
rows	`Number`	Yes	Positive number specifying the array's row count
cols	`Number`	Yes	Positive number specifying the array's column count
lambda(row, col)	`LAMBDA`	Yes	Custom LAMBDA function called for each cell, receiving current row and column indices

Using the MAKEARRAY Function

MAKEARRAY transforms LAMBDA functions into complete dynamic arrays by automatically applying the lambda to every cell position. Perfect for generating multiplication tables, random data grids, conditional matrices, or any patterned data structure where each cell value depends on its row and column position.

Common MAKEARRAY Examples

Multiplication Table Generator

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

Creates a 5x5 multiplication table where each cell shows row × column (1-5 indexing)

Random Color Matrix

=MAKEARRAY(4,3,LAMBDA(row,col,CHOOSE(RANDBETWEEN(1,3),"Red","Blue","Green")))

Generates 4×3 grid of random colors from predefined list

Distance Matrix

=MAKEARRAY(3,3,LAMBDA(r,c,ABS(r-c)))

Builds 3×3 matrix showing absolute distance between row and column indices

Frequently Asked Questions

#VALUE! error - both must be positive numbers greater than zero.

Indices start at 1 (first row=1, first column=1).

Yes, any formula returning a positive number works.

Common Errors and Solutions

#VALUE! - Incorrect Parameters

Cause: LAMBDA function doesn't accept exactly two parameters (row, col)

Solution: Ensure LAMBDA has signature LAMBDA(row,col,...)

#VALUE! - Invalid dimensions

Cause: rows or cols ≤ 0 or non-numeric

Solution: Use positive numbers only

#SPILL!

Cause: Output area blocked by data

Solution: Clear sufficient space for array spill

Notes

Row/col indices always start at 1
Maximum array size limited by available memory
LAMBDA must return single value per cell
Works with dynamic row/col sizes from formulas

Compatibility

Available in: Excel 365, Excel 2021

Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021