How does MAKEARRAY differ from SEQUENCE?

SEQUENCE can only generate numeric progressions, while MAKEARRAY lets you compute any value—text, dates, or formulas—by supplying a custom LAMBDA that runs for each cell.

Can MAKEARRAY return text strings or dates?

Yes. The lambda can return any data type that a normal cell can hold, so you can produce strings, dates, booleans, or even error values directly from MAKEARRAY.

Why do I get #SPILL! even though the surrounding cells appear empty?

Hidden objects such as tables, merged cells, or cells with data validation can block the spill range. Check for any table boundaries or merged areas that intersect the expected output.

Is MAKEARRAY a volatile function?

No. MAKEARRAY recalculates only when one of its arguments changes (rows, cols, or the lambda’s referenced cells). It does not recalculate on every workbook change like NOW() or RAND().

MAKEARRAY function

MAKEARRAY builds a custom-sized array by applying a LAMBDA to each row-column coordinate, letting you compute any value from source data.

=MAKEARRAY(rows, cols, lambda)

Generate a MAKEARRAY formula

Describe what you need. The generator will reach for MAKEARRAY where MAKEARRAY is the right tool, and tell you when it is not.

Arguments

How MAKEARRAY reads its arguments

Argument	Required	Description
rows	Required	Number of rows to generate; must be a non-negative integer – if omitted or negative, MAKEARRAY throws an error.
cols	Required	Number of columns to generate; also a non-negative integer – a zero value yields an empty array.
lambda	Required	A LAMBDA that receives the current row and column indexes (starting at 1) and must return a single scalar for each cell.

Returns

It returns a dynamic array of the specified rows and columns.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Extract Ticket IDs and Assigned Agents

Ticket ID	Priority	Opened	Closed	Agent	CSAT
101	High	2023-09-01	2023-09-02	Alice	5

=MAKEARRAY(ROWS(Tickets!A2:A6),2,LAMBDA(r,c,IF(c=1,INDEX(Tickets!A2:A6,r,1),INDEX(Tickets!E2:E6,r,1))))

Result: 101Alice102Bob103Alice104Charlie105Bob

The lambda receives a row number (r) and a column selector (c). When c equals 1 it pulls the Ticket ID from column A; otherwise it pulls the Agent name from column E. MAKEARRAY repeats this logic for each of the five data rows, producing a two-column array that pairs each ticket with its owner.

2. Calculate Resolution Time in Days

Ticket ID	Priority	Opened	Closed	Agent	CSAT
101	High	2023-09-01	2023-09-02	Alice	5

=MAKEARRAY(ROWS(Tickets!C2:C6),1,LAMBDA(r,c,DATEDIF(INDEX(Tickets!C2:C6,r,1),INDEX(Tickets!D2:D6,r,1),"d")))

Result: 14122

Here the lambda calculates the difference in days between the Opened (column C) and Closed (column D) dates for each row using DATEDIF. Because the column count is set to 1, MAKEARRAY returns a single-column vertical array where each element is the ticket's resolution time. The numbers line up with the sample data: ticket 101 closed in one day, ticket 102 took four days, and so on.

3. Average CSAT by Priority

Ticket ID	Priority	Opened	Closed	Agent	CSAT
101	High	2023-09-01	2023-09-02	Alice	5

=MAKEARRAY(3,2,LAMBDA(r,c,IF(c=1,CHOOSE(r,"High","Medium","Low"),AVERAGEIFS(Tickets!F2:F6,Tickets!B2:B6,CHOOSE(r,"High","Medium","Low")))))

Result: High4Medium3Low4.5

The lambda builds a 3-row by 2-column table. Column 1 uses CHOOSE to emit the three priority labels in order. Column 2 calculates the average CSAT for each priority with AVERAGEIFS, pulling CSAT scores from column F and matching the current priority label. The final matrix shows that High-priority tickets average a CSAT of 4, Medium a 3, and Low a 4.5.

Common errors

Which MAKEARRAY error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The lambda returns an array or range instead of a single scalar for a cell, which MAKEARRAY cannot place into the result matrix.	Wrap the expression in an aggregation (e.g., SUM, INDEX) so that the lambda yields only one value per row/column.
#SPILL!	The output array would overwrite existing data or a table, so Excel cannot spill the result.	Move the formula to a clear area, delete or relocate the obstructing cells, or use the @ operator to force a single-cell return.
#NUM!	The rows or cols argument exceeds the sheet’s maximum size (over 1,048,576 rows or 16,384 columns) or is a negative number.	Supply a realistic row/column count that matches the size of your source data, or use COUNTA/ROWS to calculate it dynamically.

Tips and when to use something else

Use BYROW or BYCOL when you need to apply a function to whole rows or columns – they are often shorter than a full MAKEARRAY construction.
If you only need to filter or reshape data, consider FILTER or CHOOSECOLS; MAKEARRAY is overkill for simple column selections.
Combine MAKEARRAY with LET to store intermediate ranges or constants, improving readability and performance.
When you just need a static grid of sequential numbers, SEQUENCE is faster and requires no LAMBDA.

Related functions

LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.BYCOLBYCOL applies a LAMBDA to each column of an array, returning a new array that aggregates or transforms column-wise results.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

How does MAKEARRAY differ from SEQUENCE?: SEQUENCE can only generate numeric progressions, while MAKEARRAY lets you compute any value—text, dates, or formulas—by supplying a custom LAMBDA that runs for each cell.
Can MAKEARRAY return text strings or dates?: Yes. The lambda can return any data type that a normal cell can hold, so you can produce strings, dates, booleans, or even error values directly from MAKEARRAY.
Why do I get #SPILL! even though the surrounding cells appear empty?: Hidden objects such as tables, merged cells, or cells with data validation can block the spill range. Check for any table boundaries or merged areas that intersect the expected output.
Is MAKEARRAY a volatile function?: No. MAKEARRAY recalculates only when one of its arguments changes (rows, cols, or the lambda’s referenced cells). It does not recalculate on every workbook change like NOW() or RAND().

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator