The MAKEARRAY function in Excel provides a powerful way to create dynamic arrays of specified row and column sizes. By leveraging the LAMBDA function, users can easily compute values in each cell of the array based on custom logic, making data manipulation more efficient and flexible.

Syntax

MAKEARRAY(rows, columns, lambda) 

• rows: The number of rows you want in the output array.
• columns: The number of columns you want in the output array.
• lambda: A LAMBDA function that takes two parameters representing the row index and column index to calculate the value for each cell.

Example #1

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

This function generates a 3×3 array where each cell value is the sum of its row and column indices. The result would look like this: 1 2 3 2 3 4 3 4 5

Example #2

MAKEARRAY(2, 4, LAMBDA(r, c, r  c))

This creates a 2×4 array where each cell value is the product of its row and column indices. The output would be: 0 0 0 0 0 1 2 3

Example #3

MAKEARRAY(2, 2, LAMBDA(r, c, IF(AND(r = c), "Diagonal", "Non-Diagonal")))

This produces a 2×2 array that labels cells as either “Diagonal” or “Non-Diagonal” based on their indices: Diagonal Non-Diagonal Non-Diagonal Diagonal

Error handling

• VALUE!: This error occurs if the “rows” or “columns” parameter is non-numeric or negative.
• NAME?: This error indicates that the provided LAMBDA function name is not recognized or incorrectly defined.
• SPILL!: This error arises if the output array isn’t able to fit in the available cell space below the formula cell.

Conclusion

The MAKEARRAY function represents a significant enhancement in Excel’s ability to handle arrays, empowering users to generate complex data sets using simple and dynamic LAMBDA functions. By understanding its syntax and leveraging its potential, users can optimize their data analysis workflows and achieve greater flexibility in their Excel tasks.