Excel functions / Logical
MAKEARRAY function
Builds an array of a set size, working out each cell from its row and column number.
Syntax
=MAKEARRAY(rows, columns, lambda)
Arguments
rows: How many rows.columns: How many columns.lambda: A LAMBDA with two inputs: the row number and the column number.
When to use it
MAKEARRAY builds an array of a given number of rows and columns, working out each cell with a LAMBDA that is given its row and column number. Times tables, calendars and test grids come out of one formula.
Examples
A times table
A B C 1 Table 2 1 2 3 3 2 4 6 4 3 6 9 In A2:
=MAKEARRAY(3, 3, LAMBDA(r, c, r*c))→ 1, 2, 3; 2, 4, 6; 3, 6, 9
Common mistakes
- The LAMBDA takes two names. The LAMBDA is given the row number and the column number, in that order.
Try MAKEARRAY in the playground
Related functions
- SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.
- MAP: Runs a function on each value of an array and returns the results in the same shape.
- LAMBDA: Makes a function of your own: names for its inputs, then the calculation.
- EXPAND: Grows an array to a set number of rows and columns, filling the new cells.