Ravindra BagaleCourses & study guides Track your progress

Excel MAKEARRAY Function

By · Last updated:

Chala mitrano — today we learn MAKEARRAY. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 with LAMBDA.
Fictional sample data for learning. Fictional seating and price grids for learning.

Purpose

Build a new array from row and column index numbers using a LAMBDA.

Return value

A rows×cols array computed by your LAMBDA(r, c).

Syntax

MAKEARRAY(rows, cols, lambda)

Arguments

ArgumentRequired?Description
rowsRequiredNumber of rows to create.
colsRequiredNumber of columns to create.
lambdaRequiredLAMBDA(r, c) using 1-based indexes.

Version

Microsoft 365 with LAMBDA.

How to use — Why → What → How

Why: You need a grid that is calculated (times table, seats, random layout) without typing each cell.

What: Excel calls your LAMBDA once per cell with that cell’s row and column index.

How — numbered steps:

  1. Try =MAKEARRAY(5, 5, LAMBDA(r,c, r*c)) for a times table.
  2. Indexes start at 1.
  3. Use IF inside to draw patterns.
  4. Keep rows*cols reasonable — huge arrays are slow.
  5. Name the LAMBDA if reused.

Ravindra Bagale's Tip

Friends, mAKEARRAY is powerful but easy to overbuild. Start 3×3.

Ravindra Bagale's Tip

r and c are indexes, not values from another sheet (unless you INDEX inside).

Worked examples

1) 5×5 times table

Teaching aid.

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

Result: Multiplication grid

What it means: r*c at each cell.

2) Serial numbers

1 column.

Formula: =MAKEARRAY(10,1, LAMBDA(r,c, r))

Result: 1..10

What it means: Like SEQUENCE.

3) Seat labels

Rows A–C feel: "R"&r&"C"&c.

Formula: =MAKEARRAY(3,4, LAMBDA(r,c, "R"&r&"C"&c))

Result: R1C1…

What it means: Text grid.

4) Checker pattern

1/0.

Formula: =MAKEARRAY(4,4, LAMBDA(r,c, MOD(r+c,2)))

Result: 0/1 pattern

What it means: MOD pattern.

5) Price matrix ₹

Base 100 + 10*r + c.

Formula: =MAKEARRAY(3,3, LAMBDA(r,c, 100+10*r+c))

Result: Price grid

What it means: Simple pricing toy.

Common errors

#VALUE!: Non-integer or negative sizes.
#SPILL!: Blocked output.
#NUM!: Absurdly large dimensions.

Practice tasks (10+)

  1. Task 1. Build a 10×10 times table.
    Hint: try on a tiny range first
  2. Task 2. MAKEARRAY of SEQUENCE-like numbers.
    Hint: try on a tiny range first
  3. Task 3. Text labels for seats.
    Hint: try on a tiny range first
  4. Task 4. Random integers with RANDBETWEEN inside (volatile!).
    Hint: try on a tiny range first
  5. Task 5. Explain vs SEQUENCE/RANDARRAY.
    Hint: try on a tiny range first
  6. Task 6. Pattern with MOD.
    Hint: try on a tiny range first
  7. Task 7. Interview: when MAKEARRAY beats typing.
    Hint: try on a tiny range first
  8. Task 8. Cap size to avoid freezes.
    Hint: try on a tiny range first
  9. Task 9. Combine with ROUND.
    Hint: try on a tiny range first
  10. Task 10. Bonus: MAKEARRAY that INDEX-es another table.
    Hint: try on a tiny range first

Quick quiz

Q1. MAKEARRAY uses LAMBDA(r,c).
Q2. Indexes are 1-based.
Q3. Works in Excel 2016 without 365.
Q4. Huge rows×cols can slow Excel.

Answers: Q1=True, Q2=True, Q3=False, Q4=True

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path