How to Use MAKEARRAY Function in Excel + Tips

Hello everyone! Today i will show you how to use MAKEARRAY function in Microsoft Excel step by step. MAKEARRAY function available in Excel 365, Excel 2024, and Excel for Web.

1. What is MAKEARRAY Function in Excel?

The MAKEARRAY function is a dynamic array function in Excel. It allows you to create a grid or array of rows and columns with custom calculation using LAMBDA function.

Think of MAKEARRAY like a custom grid generator:

• You choose how many rows and columns you want.
• You give a rule LAMBDA(r, c, ...) to calculate every position cell.
• Excel automatically builds the whole table for you in one formula!

2. 🛠️ Formula Syntax & Parameters

The MAKEARRAY formula syntax is as follows:

=MAKEARRAY(rows, columns, lambda)

• rows: Required. The number of rows in the array (must be greater than 0).
• columns: Required. The number of columns in the array (must be greater than 0).
• lambda: Required. A LAMBDA function that takes 2 parameters: LAMBDA(row, col, calculation).
  • row: The current row index (from 1 to rows).
  • col: The current column index (from 1 to columns).

3. 📋 Practical Examples

Example 1: Create a Simple Multiplication Table

You can build a 5x5 multiplication table using row index multiplied by column index:

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

👉 Result: Generates a 5-row by 5-column table showing r * c:

Example 2: Generate Cell Coordinates Label (R1C1 Style)

Create a 3x3 table showing position label for each cell:

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

👉 Result:

Example 3: Create Sequential Numbers Grid (4 Rows x 3 Columns)

You can calculate sequential sequence numbers across rows and columns:

=MAKEARRAY(4, 3, LAMBDA(r, c, (r - 1) * 3 + c))

👉 Result: Fills numbers from 1 to 12 across 4 rows and 3 columns:

Example 4: Generate Random Test Scores Table

Quickly create 3x3 grid of random numbers between 50 and 100:

=MAKEARRAY(3, 3, LAMBDA(r, c, RANDBETWEEN(50, 100)))

👉 Result: Automatically fills a 3x3 table with random scores!

💡 Quick Tips for Using MAKEARRAY Function

1. LAMBDA Must Have 2 Arguments Always: The LAMBDA inside MAKEARRAY MUST always take exactly 2 arguments (like r for row, c for column). Even if your calculation only uses r or c, you cannot omit the second parameter!
2. Row and Column Numbers Start at 1: r always starts at 1 (first row) and c always starts at 1 (first column).
3. Great for Mock Data & Matrices: Combine MAKEARRAY with RANDBETWEEN() or IF() to generate test datasets or identity matrices instantly without dragging formulas down.
4. Excel Version Requirement: MAKEARRAY requires Microsoft 365, Excel 2024, or Excel for Web.

📚 Related Resources & Tutorials

• How to Use MAP Function in Excel
• How to Use LAMBDA Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗