The MAKEARRAY function in Google Sheets is a powerful tool designed to generate a multidimensional array. This function enables users to create arrays with specified dimensions while applying complex calculations through the use of a LAMBDA function. It enhances the flexibility and capability of spreadsheets to represent data dynamically.

Syntax

MAKEARRAY(row_count, column_count, lambda)

• row_count: The number of rows in the resulting array.
• column_count: The number of columns in the resulting array.
• lambda: A LAMBDA function that defines how each element in the array is calculated.

Example #1

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

This function call generates a 3×3 array where each element is represented as “row,column”. The result would be: 1,1 | 1,2 | 1,3 2,1 | 2,2 | 2,3 3,1 | 3,2 | 3,3

Example #2

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

This function creates a 2×2 array where each element is the sum of its row and column index. The result would be: 0 | 1 1 | 2

Example #3

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

This function generates a 2×3 array where each element is the product of its row and column indices. The result would be: 0 | 0 | 0 0 | 1 | 2

Error handling

• VALUE! – This error occurs if the lambda function does not return a single value for each cell in the array.
• REF! – This error appears when the row or column count provided is invalid or results in an array exceeding spreadsheet limits.
• NAME? – This indicates that the LAMBDA function syntax is incorrect or misspelled.

Conclusion

The MAKEARRAY function significantly enhances data manipulation within Google Sheets by allowing the creation of complex, calculated arrays. With its ability to integrate LAMBDA functions, users can develop dynamic datasets tailored to their needs, making it an invaluable resource for anyone looking to optimize their spreadsheet capabilities.