MAKEARRAY function

MAKEARRAY allows you to easily generate an array (range) of values. The first two arguments are the number of rows and columns, and further should specify the expression within which the calculation is performed using the LAMBDA function. This function is also dynamic in nature because it does not return a single value but a range in accordance with the given dimensions.

The syntax of this function is:

MAKEARRAY (<number of rows>, <number of columns>, <calculation>)

The easiest way to see how this function works is to generate a multiplication table. By entering the formula:

=MAKEARRAY(10;10;LAMBDA(x;y;x*y))

Excel will generate a multiplication table of dimensions 10×10.

Of course, as a last argument, it is possible to give a more complex calculation. For example, if we want to create a table with test data using the CHOOSE and RANDBETWEEN functions, we can randomly generate the name of one of the three given cities:

=MAKEARRAY(10,1,LAMBDA(x,y,CHOOSE(RANDBETWEEN(1,3),”Beograd”,”Novi Sad”,”Niš”)))

To generate sales values in a random way we should enter the formula:

=MAKEARRAY(10,1,LAMBDA(x,y,RANDBETWEEN(10,50)*5000))

As you can see in just two steps we came to a table with test data that is dynamic and changes each time we refresh the calculation, by pressing the F9 key.