You can use the MAKEARRAY function in Excel to create an array of values with a specified number of rows and columns.
This function uses the following basic syntax:
=MAKEARRAY(rows, cols, lambda(row, col))
where:
- rows: Number of rows in the array
- col: Number of columns in the array
- lambda: A function that specifies the values to fill the array with
The following examples show several different ways to use the MAKEARRAY function in practice in Excel.
Example 1: Create Array with Values Created from Formula
We can type the following formula into cell A1 to generate an array with 5 rows and 3 columns in which the values in each cell of the array are calculated as 2 * row number * column number:
=MAKEARRAY(5, 3, LAMBDA(r,c, 2*r*c))
The following screenshot shows how to use this formula in practice:
The resulting array contains 5 rows and 3 columns.
The values within the array are calculated as: 2 * row number * column number
For example:
- The value in row 1 and column 1: is 2 * 1 * 1 = 2
- The value in row 2 and column 1: is 2 * 2 * 1 = 4
- The value in row 3 and column 1: is 2 * 3 * 1 = 6
And so on.
Example 2: Create Array with Random Text Values
We can type the following formula into cell A1 to generate an array with 15 rows and 1 column in which the value in each cell of the array is one random basketball team name chosen from a list of three possible names:
=MAKEARRAY(15,1,LAMBDA(r,c,CHOOSE(RANDBETWEEN(1,3),"Mavs","Hawks","Kings")))
The following screenshot shows how to use this formula in practice:
The resulting array contains 15 rows and 1 column.
The value in each cell of the array is randomly chosen to be Mavs, Hawks or Kings.
Example 3: Create Array with Random Numeric Values
We can type the following formula into cell A1 to generate an array with 10 rows and 2 columns in which the value in each cell of the array is a random integer between 1 and 100:
=MAKEARRAY(10, 2, LAMBDA(r,c, RANDBETWEEN(1,100)))
The following screenshot shows how to use this formula in practice:
The resulting array contains 10 rows and 2 columns.
The value in each cell of the array is a randomly chosen integer between 1 and 100.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Multiply Column by a Percentage
Excel: How to Multiply Column by a Constant
Excel: How to Use a MULTIPLY IF Function
