MAKEARRAYformula creates a new array with the specified number of rows and columns, and populates each cell with the result of the provided lambda function. This formula is useful for generating arrays with custom values based on a formula or calculation.
- How to use
- Examples of using
MAKEARRAYformula not working?
- Similar formulas to
MAKEARRAY formula with the syntax shown below, it has 3 required parameters:
- rows (required):
The number of rows in the new array. Must be a positive integer.
- columns (required):
The number of columns in the new array. Must be a positive integer.
- LAMBDA (required):
A lambda function that returns the value to be populated in each cell of the new array. The function should take two arguments, row and column, which are the 0-based indices of the current cell.
ExamplesHere are a few example use cases that explain how to use the
MAKEARRAYformula in Google Sheets.
Creating a matrix of zeros
To create a matrix of zeros with a specific number of rows and columns, you can use the
MAKEARRAY formula with a lambda function that returns 0 for every cell.
Generating a random matrix
You can use the
RANDBETWEEN function inside a lambda function to generate a matrix of random integers within a specific range.
Transposing a matrix
You can use the
MAKEARRAY formula with a lambda function that returns the value of the corresponding cell in the original matrix transposed.
MAKEARRAYnot working? Here are some common mistakes people make when using the
MAKEARRAYGoogle Sheets Formula:
Not providing the correct number of rows and columns
Make sure to provide the correct number of rows and columns when using
MAKEARRAY, or the resulting array may not have the desired dimensions.
Using an invalid lambda function
If using a lambda function to generate the values in the array, make sure the function is valid and returns the desired output.
The following functions are similar to
MAKEARRAY or are often used with it in a formula:
ARRAYFORMULAformula in Google Sheets allows for the application of a formula to an entire column or range of data, without needing to manually copy the formula to each cell. It is most commonly used to simplify and automate complex calculations across large data sets.
TRANSPOSEformula in Google Sheets allows you to flip a table of data by turning rows into columns and columns into rows. This can be useful for reorganizing data or preparing it for use in other formulas.
INDEXformula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.
MATCHformula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
You can learn more about the
MAKEARRAY Google Sheets function on Google Support.