What is MAKEARRAY in Google Sheets?
MAKEARRAY creates a grid of specified height and width, computing each cell with a LAMBDA that receives row and column indexes. It builds lookup tables, sensitivity matrices, and coordinate-driven templates without manual fill.
When to use it
Use MAKEARRAY when each cell depends on its row and column index, such as times tables or scenario grids.
When to skip it
Skip MAKEARRAY for a simple sequential list. SEQUENCE or ROW/COLUMN combos are lighter.
How it works
-
1
Set row count and column count for the output matrix.
-
2
Write LAMBDA(row_index, col_index, formula) using 1-based indexes.
-
3
Return the cell value from the LAMBDA for that position.
-
4
Let the result spill as a block with room on the sheet.
-
5
Combine with XLOOKUP inputs on edges for two-way lookup tables.
-
6
Keep LAMBDA bodies fast because rows times columns calls add up.
Examples in Google Sheets
Multiplication table
=MAKEARRAY(12, 12, LAMBDA(r, c, r*c)) fills a times table in one formula.
Coordinate labels
=MAKEARRAY(5, 3, LAMBDA(r, c, "R"&r&"C"&c)) prints grid addresses for a layout mock.
Fee schedule
=MAKEARRAY(10, 4, LAMBDA(r, c, INDEX(fee_row, c)*r)) builds fees by tier column and volume row.
Better Sheets resources
Common mistakes
-
Using MAKEARRAY for one column when SEQUENCE suffices.
-
Heavy external references inside LAMBDA called hundreds of times.
-
Off-by-one errors with 1-based row and column indexes.
-
Placing spill over merged cells or existing charts.
-
Expecting MAKEARRAY to read existing grid values without INDEX lookups.
Frequently asked questions
- MAKEARRAY vs SEQUENCE?
- SEQUENCE fills simple sequences. MAKEARRAY computes each cell from row and column index.
- Index start at 1?
- Yes. Row and column parameters begin at one in standard use.
- MAKEARRAY size limits?
- Very large grids are slow and may hit spill limits. Test size first.
- MAKEARRAY and LAMBDA?
- MAKEARRAY requires a LAMBDA as the third argument.
- Can cells reference sheet data?
- Yes via INDEX or OFFSET inside the LAMBDA using r and c.
- MAKEARRAY errors?
- Often blocked spill or invalid LAMBDA return type.
- MAKEARRAY vs manual table?
- MAKEARRAY stays dynamic when inputs or dimensions change.
Related Tutorials
Watch how MAKEARRAY works
Spreadsheet Automation 101 Lesson 2: Arrays
MAKE Your Google Sheets Add-on WORK
Related blog posts
Guides that explain MAKEARRAY in more depth.
How to create a formula for every row in a column in a Google Spreadsheet?
3 possible ways to create a formula for every row in a column in a Google Spreadsheet. And two bonus ways to make it look nicer. - Auto fill - Double Click - ArrayFormula
Read post →How to Gain Confidence in Google Sheets
Boost your confidence, master formula usage and overcome errors in this step-by-step guide. Join us to demystify spreadsheets.
Read post →Related terms
SEQUENCE
SEQUENCE returns a grid of sequential numbers. You set how many rows and columns, the starting value, and the step between values. It replaces manual 1, 2, 3 fill patterns and pairs well with MAP, LAMBDA, and date math for generated row labels.
Read guide →LAMBDA
LAMBDA defines an anonymous function inside a formula. You name parameters, write the calculation, then pass arguments when you call it. Combined with MAP, BYROW, or a named LAMBDA in the Name manager, it replaces fragile copy-paste logic across big tables.
Read guide →MAP
MAP walks aligned arrays element by element and applies a LAMBDA to each position. Pass one array for unary transforms or several arrays for element-wise combinations. It is the modern replacement for many ARRAYFORMULA column operations.
Read guide →TOCOL
TOCOL converts a range into a single column. You can choose whether it reads down each column first or across rows first. It replaces manual paste-special transpose chains when reshaping blocks for UNIQUE or FILTER.
Read guide →Done reading about MAKEARRAY?
Membership unlocks 600+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.