The MAKEARRAY function takes three arguments: rows, columns, and lambda. Rows is the number of columns to create, and columns is the number or columns to create. Lambda is the calculation to use when creating values in the array. The total number of values in the array returned by MAKEARRAY will equal rows * columns.
LAMBDA structure
The MAKEARRAY uses the LAMBDA function to apply the formula required to calculate array values. The structure of the LAMBDA used by MAKEARRAY is: where r represents the row count, and c represents the column count originally passed into MAKEARRAY, and calculation is the formula needed to create the values in the final array. Note: to generate an array with sequential values, see the SEQUENCE function.
Examples
In the formula below, MAKEARRAY is used to create an array with 2 rows and 3 columns, populated with the result multiplying rows by columns: The result is a 2 x 3 array with six values {1,2,3;2,4,6}. The calculation can be hardcoded. Below are examples of the same formula, with calculation hardcoded as zero and “x”:
Random values
MAKEARRAY can be used to generate random values. In the formula below. The CHAR function is used with the RANDBETWEEN function to generate random uppercase letters A-Z: The result is a 2 x 3 array like: {“D”,“Q”,“F”;“V”,“C”,“T”}.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
