MAKEARRAY function

The MAKEARRAY function in Excel is a dynamic array function introduced in Excel 365. It allows you to create arrays based on specific dimensions and fill them with calculated values. The MAKEARRAY function generates an array where each element can be calculated using a custom formula or logic.

Syntax:

=MAKEARRAY(rows, columns, lambda)

• rows: The number of rows in the array.
• columns: The number of columns in the array.
• lambda: A LAMBDA function that defines the value for each element of the array. The LAMBDA function should have two parameters representing the row and column indices.

How it works:

• You specify the size of the array (number of rows and columns).
• The lambda function is used to calculate each element in the array. The lambda function receives two parameters: the row index (r) and the column index (c), and you use these to define the calculation for each cell.

Example 1: Simple Array

If you want to create a 3×3 array where each element is the sum of its row and column index:

=MAKEARRAY(3, 3, LAMBDA(r, c, r + c))

This creates a 3×3 array like this:

3  4  5
4  5  6
5  6  7

Explanation:

• For the first element (row 1, column 1), r = 1 and c = 1, so the value is 1 + 1 = 2.
• For the second element (row 1, column 2), r = 1 and c = 2, so the value is 1 + 2 = 3, and so on.

Example 2: Array with a Formula

You can use more complex calculations in the lambda function. For instance, creating a multiplication table:

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

This will create a 5×5 multiplication table:

1   2   3   4   5
2   4   6   8  10
3   6   9  12  15
4   8  12  16  20
5  10  15  20  25

Example 3: Using MAKEARRAY with Condition

You can also apply conditional logic in the lambda function. For example, creating an array where each element is “Even” or “Odd” based on whether the number is even or odd:

=MAKEARRAY(3, 3, LAMBDA(r, c, IF(MOD(r + c, 2) = 0, "Even", "Odd")))

This will generate the following array:

Even  Odd  Even
Odd   Even Odd
Even  Odd  Even

Benefits of MAKEARRAY:

1. Dynamic Arrays: It allows you to generate dynamic arrays based on a given size and logic, without needing to manually create them.
2. Custom Calculation: You can use the LAMBDA function to define custom formulas and logic for each element.
3. Flexibility: You can generate arrays of any size (up to 2^20 rows or columns) and perform various types of calculations, from simple arithmetic to complex logic.

Use Cases:

• Generating Data: Quickly create large arrays with custom data, useful for simulations or generating test data.
• Matrix Calculations: Generate matrices for various mathematical or statistical operations.
• Random Data: Create arrays of random numbers or data that follow a specific pattern.

The MAKEARRAY function is a powerful tool for dynamically creating and working with arrays in Excel, especially when combined with LAMBDA for more complex logic.