Function Problems

How to Use Excel Dynamic Arrays to Match Entities and Multiply Costs

Amos GikundaAmos Gikunda Nov 10, 2026 870 views

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.

How to Match Entities and Multiply Costs Using Excel Dynamic Array Formulas
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.
Before you start

Ensure you are using a version of Excel or WPS Office that supports modern dynamic array functions like SEQUENCE, XMATCH, and spilled array behaviors.

Solution 1Recommended

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.

1
Identify your data ranges

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).

2
Build the conditional fixed cost logic

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.

3
Add the dynamic lookup for variable costs

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.

4
Multiply by the base costs

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 a Combined Dynamic Array Formula with INDEX and XMATCH
Handling Missing Entities: Wrap the INDEX function in IFNA to prevent #N/A errors if an entity code in your base data does not exist in the headcount lookup table.
Efficient Expense Allocation

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. 1. Open your financial spreadsheet: Launch WPS Spreadsheet and open the document containing your cost allocation data.
  2. 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. 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.
Full compatibility with Microsoft Excel dynamic array functions like XMATCH and SEQUENCE.Spill complex matrix formulas across multiple cells with a single keystroke.Lightweight installation and blazing fast calculation speeds for large datasets.Free and user-friendly interface identical to the classic spreadsheet layout.
microsoft office alternative - wps office

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.