How to Use MAKEARRAY Function in Excel (3 Examples)


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:

Excel MAKEARRAY function example

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:

Excel MAKEARRAY function to generate array of text values

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:

Excel MAKEARARY function to create array with random numeric values

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

Leave a Reply

Your email address will not be published. Required fields are marked *