Solved: How do I copy an array N amount of times via formula, stacking it in the end of other

📌 The Problem Explained

Imagine you have a list of five product categories and you need to repeat that exact list 12 times to create a row for every month of the year. Manually copying and pasting the range is inefficient and creates a “static” list that won’t update if your product categories change.

In modern Excel, users often need a way to “loop” an array vertically so that if the source data or the multiplier ($N$) changes, the stacked list updates automatically.

💡 The Quick Solution

Use the following formula (assuming your data is in A2:B4 and your multiplier $N$ is in cell D1):

=CHOOSEROWS(A2:B4, MOD(SEQUENCE(ROWS(A2:B4) * D1) - 1, ROWS(A2:B4)) + 1)

🛠️ Step-by-Step Breakdown

To implement this, follow these steps:

1. Identify your source range (e.g., A2:B10).
2. Identify or input your multiplier $N$ in a separate cell (e.g., D1).
3. Enter the formula in the cell where you want the stacked array to begin.

Function/Argument	Purpose
ROWS(A2:B4)	Counts how many rows are in your original source array.
SEQUENCE(…)	Creates a vertical list of numbers from 1 to the total number of rows (Original Rows × $N$).
MOD(…, Rows) + 1	A mathematical trick that resets the counter to 1 every time it reaches the end of your original list.
CHOOSEROWS(…)	Takes the original array and pulls the specific row numbers generated by the MOD/SEQUENCE logic.

⚠️ Common Mistakes to Avoid

• Absolute vs. Relative References: If you plan to move the formula, ensure your source range (e.g., $A$2:$B$4) is locked with dollar signs so the reference doesn’t shift.
• The “Zero” Index Error: The MOD function can return a 0, but Excel rows start at 1. Ensure you use the - 1 inside the MOD and the + 1 outside to keep the index valid for the CHOOSEROWS function.
• Spill Errors: Ensure there is enough empty space below the formula cell. If existing data blocks the path, Excel will return a #SPILL! error.