How to Use Excel Dynamic Arrays to Match Entities and Multiply Costs
Question details
The user needs to allocate expenses across multiple periods using dynamic arrays, multiplying variable costs by matching entity headcounts while keeping fixed costs unadjusted.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Financial modeling or expense allocation across different departments and time periods.
- Observed behavior
- Calculating a spilled array of distributed costs dynamically using a single formula.
Ensure you are using a version of Excel or WPS Office that supports modern dynamic array functions like SEQUENCE, XMATCH, and spilled array behaviors.
Use a Combined Dynamic Array Formula with INDEX and XMATCH
Create a spilled headcount matrix to dynamically allocate expenses over multiple periods while logically excluding fixed costs from the multiplication.
By combining INDEX, XMATCH, and SEQUENCE with an IF statement, you can spill a two-dimensional array of headcounts by entity for each expense item. This allows you to apply a multiplier conditionally depending on whether the expense type is variable or fixed.
Ensure your expense types (e.g., C10:C16), base costs (D10:D16), and entity codes (E10:E16) are clearly structured in columns. Also, locate your headcount lookup table (e.g., H5:L6 for headcounts and E5:E6 for the matching lookup entities).
Start your formula in the top-left cell of the output range with the IF function to identify fixed costs. Enter =IF(C10:C16="Fixed", 1, ...). This ensures fixed costs are only multiplied by 1 instead of headcount.
In the false part of the IF statement, nest your lookup array: INDEX(H5:L6,XMATCH(E10:E16,E5:E6),SEQUENCE(,COLUMNS(H5:L6))). This pulls the correct row of headcounts for the matched entity across all periods.
Multiply the entire IF statement by your base cost array. The final formula looks like this: =IF(C10:C16="Fixed",1,INDEX(H5:L6,XMATCH(E10:E16,E5:E6),SEQUENCE(,COLUMNS(H5:L6))))*D10:D16. Press Enter to spill the results into the adjacent cells.

Use WPS Spreadsheet for Advanced Dynamic Array Formulas
WPS Office fully supports dynamic arrays, enabling you to build complex financial models and allocate costs effortlessly. Enjoy seamless compatibility with Microsoft Excel functions in a lightweight, free office suite.
- 1. Open your financial spreadsheet: Launch WPS Spreadsheet and open the document containing your cost allocation data.
- 2. Select the target output cell: Click on the top-left cell of the blank range where you want the calculated multi-period costs to appear.
- 3. Enter the spilled formula: Type your combined INDEX and XMATCH formula and press Enter. WPS Spreadsheet will automatically calculate and spill the matrix across rows and columns.

Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the output range required for your dynamic array is blocked by existing data, spaces, or merged cells. Clear the cells in the intended spill area to resolve the error.
Can I use VLOOKUP instead of INDEX and XMATCH for this scenario?
While VLOOKUP can be used for simple one-column lookups, the INDEX function combined with XMATCH and SEQUENCE is specifically required here to return a two-dimensional array across multiple periods for a spilled result.
What if my expense items have different growth rates instead of headcount?
You can replace the headcount lookup table with a growth rate matrix. Ensure the dimensions match the time periods you are projecting, and simply adjust the XMATCH entity references to match your growth rate identifiers.




