How to Expand a Dynamic Named Range into Repeated Options in Excel
Question details
The user needs to create a dynamic table by expanding a named range of insurance plans so that each plan repeats four times alongside specific coverage options.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic cross-join table from a named range of items (health, dental, vision) and a set of fixed options (EO, ESP, ECH, FAM).
- Observed behavior
- A dynamic array formula is required to automatically output a two-column array where each plan name is repeated four times and paired with the four coverage tiers.
Ensure you are using a version of Excel or WPS Office that supports modern Dynamic Array functions, including REDUCE, LAMBDA, VSTACK, HSTACK, and LET.
Use REDUCE and EXPAND Functions to Repeat Plans
This method iterates through the named range, expanding each item four times and vertically stacking the results with the corresponding options.
This formula builds the array row by row. The EXPAND function repeats each plan four times, while HSTACK pairs the expanded plan with a vertical array of the four coverage options.
Select your list of insurance plans (e.g., Health, Dental, Vision) and name the range 'InsurancePlan' using the Name Box next to the formula bar.
Click on the top-left cell where you want the new table to generate and enter: =DROP(REDUCE("",InsurancePlan,LAMBDA(a,x,VSTACK(a,HSTACK(EXPAND(x,4,1,x),VSTACK("EO","ESP","ECH","FAM"))))),1)
Press Enter. The dynamic array will automatically spill down into the adjacent cells, displaying each plan repeated four times with its coverage options.
Create a Cross-Join Using LET and TOCOL
An alternative approach that performs a basic cross-join of two vectors (Plans and Options) without using the REDUCE function.
Handle Dynamic Array Formulas Easily in WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced array functions like LET, VSTACK, and TOCOL, allowing you to execute complex data manipulations and cross-joins seamlessly.
- 1. Launch WPS Spreadsheet: Open WPS Office and create a new Spreadsheet document or open your existing workbook.
- 2. Define Your Names: Highlight your data and use the Name Manager under the Formulas tab to assign your named ranges.
- 3. Input the Array Formula: Type the dynamic array formula into your target cell and press Enter to instantly spill the data into a cross-joined table.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when there is existing text, numbers, or formatting blocking the cells where the formula needs to output its results. Clear the cells below and to the right of your formula to allow the array to expand fully.
Can I change the formula to accommodate five options instead of four?
Yes. If you add another item to the options array, update the number in the EXPAND function (change 4 to 5) and add the new option into the VSTACK array string.
Do these formulas work in all versions of Excel?
No, functions like REDUCE, LAMBDA, HSTACK, VSTACK, and TOCOL are only available in Microsoft 365, Excel 2021, and recent versions of WPS Office. Older versions will return a #NAME? error.
