MAKEARRAY in Excel: Generate Tables from Row and Column Positions

• First introduced: Preview announced July 26, 2021, for Windows Beta 2108 (build 14312.20008) and Mac 16.52 (build 21072100). These are announced preview minima. Official introduction.
• Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac. Availability depends on the installed build.
• Evidence checked: October 14, 2026. Official MAKEARRAY help. Examples below were independently calculated, not executed in Excel.

The alphabetical catalogue also carries an Excel 2024 marker, while this dedicated help page lists Microsoft 365 only. That documentation mismatch is recorded explicitly; this guide does not resolve it by assuming perpetual-edition support.

=MAKEARRAY(rows,columns,LAMBDA(row_num,col_num,calculation))

The first two arguments set the output size. The LAMBDA receives the current row and column indices, starting at 1. Use positive whole-number dimensions for the examples. The official help documents #VALUE! for dimensions below 1 or nonnumeric dimensions and for an incorrect LAMBDA parameter count. MAKEARRAY reference

Use clear parameter names such as row_num and col_num, rather than names that could be mistaken for worksheet references. These names represent positions within the generated array, not the worksheet row and column numbers where the formula is entered.

Enter this formula in B2 on an otherwise empty sheet:

=MAKEARRAY(3,4,LAMBDA(row_num,col_num,row_num*col_num))

Expected result in B2:E4:

The table heading is explanatory, not a returned row. The output contains exactly three rows and four columns. The bottom-right value is 3×4=12. Entering the same formula in H10 still starts with 1, because the indices are relative to the generated array.

=MAKEARRAY(3,3,LAMBDA(row_num,col_num,IF(row_num=col_num,1,0)))

Expected numeric output:

1  0  0
0  1  0
0  0  1

There are three ones and six zeros. The equality test is true only when the row and column indices match. This illustrates a structural rule without requiring a source table.

To test the dimensions separately, temporarily change the first argument to 2. The result is then two rows by three columns, with ones at positions (1,1) and (2,2); MAKEARRAY does not require a square matrix.

On a separate sheet, enter unit prices 2, 3 and 5 in A2:A4. Enter quantities 1, 5 and 10 across B1:D1. Put the formula in F2, away from both input ranges:

=MAKEARRAY(3,3,LAMBDA(row_num,col_num,INDEX($A$2:$A$4,row_num)*INDEX($B$1:$D$1,1,col_num)))

Expected result in F2:H4:

 2  10  20
 3  15  30
 5  25  50

The row index selects one price; the column index selects one quantity. For example, output row 2, column 3 is price 3 multiplied by quantity 10, giving 30.

The range B1:D1 is horizontal, so its INDEX call explicitly supplies row 1 and the changing column index. That prevents confusing a horizontal input with a vertical list. The three-by-three size is deliberate: increasing it without extending the corresponding source ranges would request positions that the inputs do not contain.

For that same price-grid design, the sizes can be tied to the source ranges:

=MAKEARRAY(ROWS($A$2:$A$4),COLUMNS($B$1:$D$1),LAMBDA(row_num,col_num,INDEX($A$2:$A$4,row_num)*INDEX($B$1:$D$1,1,col_num)))

It produces the same three-by-three results. This avoids maintaining a separate hard-coded dimension when you intentionally extend one of the ranges. It does not automatically remove blank prices or blank quantity cells; range design still matters.

Keep the destination spill rectangle clear. A formula placed inside its own source ranges can create a circular-reference problem unrelated to MAKEARRAY’s dimensions.

The LAMBDA must produce the intended result for each position. A lookup error in a source calculation is different from an invalid array size, so inspect the failing expression rather than treating all errors as the same issue.

• LAMBDA in Excel
• INDEX in Excel

Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.