To expand an art-asset quantity matrix into one worksheet row per item, first list each asset–format combination with a cumulative quantity. Then use SEQUENCE and XLOOKUP to repeat its labels the required number of times. This walkthrough uses synthetic artwork labels.

The task is straightforward: turn a compact planning grid into an item list. A quantity of two produces two rows with the same asset and format; zero produces none. The worked example below keeps the intermediate calculations visible so you can check the result before adapting it.

Which Excel for Mac versions support this method?

Use Excel for Microsoft 365 for Mac, Excel 2024 for Mac, or Excel 2021 for Mac. Microsoft’s documentation lists these editions for both SEQUENCE and XLOOKUP, and its Excel 2021 for Mac feature guide explicitly documents dynamic arrays. SEQUENCE function, XLOOKUP function, What’s new in Excel 2021 for Mac.

Function availability matters when copying formulas from other tutorials. For example, Microsoft lists VSTACK for Microsoft 365 and Excel 2024 on Mac, but does not list Excel 2021. The method here avoids that dependency. Microsoft also explicitly states that XLOOKUP is unavailable in Excel 2016 and Excel 2019, so those editions cannot run the formulas below unchanged. VSTACK function, XLOOKUP function.

Set up the art-asset quantity matrix

On a blank worksheet, enter the following synthetic data in A1:C4. The first column holds asset labels, the header row holds formats, and the remaining cells hold quantities.

Asset Small Large
Arc Tile 2 0
Dot Tile 1 0
Wave Tile 2 1

For this example, every quantity must be a numeric whole number of zero or greater. Enter an explicit zero when no item is needed. Resolve blanks, text quantities, negative values, fractions, and cell errors before proceeding. These formulas assume valid counts; they do not validate or round them.

The expected output contains six item rows: two small Arc Tiles, one small Dot Tile, two small Wave Tiles, and one large Wave Tile. Repeated labels are intentional because each row represents one item. The formulas create worksheet records; they do not create or duplicate artwork files.

Flatten the matrix into a checkable helper list

Leave column D empty. In E1:I1, enter the headers Position, Asset, Format, Quantity, and End item. Enter the numbers one through six in E2:E7, in order. There are six helper rows because three assets multiplied by two formats gives six combinations.

The helper list reads across each source row before moving down: Arc Tile Small, Arc Tile Large, then the two Dot Tile combinations, followed by the two Wave Tile combinations. Keeping this order consistent is essential.

Retrieve each combination’s labels and quantity

Enter this formula in F2:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Enter this formula in G2:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Enter this formula in H2:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

Copy F2:H2 down through row seven. These are ordinary cell formulas, with one result per cell. INDEX retrieves a value at a specified row and column. Here, INT makes the source row advance after every two positions, while MOD makes the source column alternate between the two formats. INDEX function, INT function, MOD function.

Calculate each combination’s final item number

In I2, enter the following formula and copy it down through I7:

=SUM($H$2:H2)

The fixed starting reference and moving ending reference create a running total. SUM adds the quantities from the first helper row through the current row. SUM function.

Your completed helper list should look like this:

Position Asset Format Quantity End item
1 Arc Tile Small 2 2
2 Arc Tile Large 0 2
3 Dot Tile Small 1 3
4 Dot Tile Large 0 3
5 Wave Tile Small 2 5
6 Wave Tile Large 1 6

Interpret End item as a boundary. The first combination occupies item numbers one and two. The third combination occupies item number three. The fifth occupies four and five, and the sixth occupies six. A zero quantity does not move the boundary forward, so it receives no item numbers.

Generate one output row per item

In K1, enter Asset; in L1, enter Format. Keep the cells beneath both headers empty. Place the output in ordinary worksheet cells outside an Excel table: Microsoft documents that spilled array formulas are unsupported inside tables. Dynamic array formulas and spilled array behavior.

Enter this complete formula in K2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

Enter this complete formula in L2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

Confirm each formula with Return. Do not copy these two formulas down: their array results fill the neighboring rows automatically. Microsoft describes this automatic expansion as spilling. Dynamic array formulas and spilled array behavior.

SEQUENCE generates the item numbers from one through the total quantity. For each number, XLOOKUP searches the cumulative boundaries. Its final two arguments specify an exact match or next larger value, searched from first to last. That search direction preserves the first matching boundary when zeros create repeated totals. SEQUENCE function, XLOOKUP function.

The outer IF returns empty text when the total is zero. In that case, the two anchor cells look blank and there are no item records to use. This is a deliberate zero-total condition, using IF to choose between two results. IF function.

For the supplied matrix, K1:L7 should contain:

Asset Format
Arc Tile Small
Arc Tile Small
Dot Tile Small
Wave Tile Small
Wave Tile Small
Wave Tile Large

Both output columns use the same generated item numbers and boundary range. Only the return range changes: column F supplies asset labels, and column G supplies formats. Keep those lookup inputs synchronized when editing the formulas.

Check the result before expanding the source

Use three checks with the sample data:

  1. Compare totals: =SUM(B2:C4) and =SUM(H2:H7) must both return six.
  2. Compare combinations: the output must contain the four nonzero combinations with their stated repetition counts.
  3. Change B2 from two to three: the expected output becomes seven rows, with three Arc Tile Small entries. Restore it to two afterward.

The first check detects omitted or duplicated quantities during flattening. The second detects misaligned labels, which a total alone cannot reveal. The third checks whether changes flow through the helper list into the output.

Add more assets or formats deliberately

Changing quantities inside B2:C4 uses the existing setup. Increasing the matrix’s dimensions requires changing the references and helper-list length.

For a complete example, expand the source to four assets and three formats in A1:D5. Move or retain the helper area at E:I, enter positions one through twelve in E2:E13, and replace the formulas in F2:H2 with:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

Copy those formulas through row thirteen, and extend the running-total formula in column I through the same row. In both output formulas, replace every helper-range ending at row seven with row thirteen. The divisor and remainder base are now three because each asset has three formats. Recheck the source total and individual combinations after making these changes.

Resolve incomplete or blocked output

If Excel displays #SPILL!, inspect the intended output area for existing entries or merged cells, and confirm that the formula is outside an Excel table. A result that extends beyond the worksheet edge also cannot spill. Microsoft documents each of these conditions. How to correct a #SPILL! error.

If the output has too few rows, inspect the quantities before changing the lookup logic. Microsoft notes that SUM ignores text in referenced ranges. A quantity stored as text can therefore be omitted from the total. Correct the source value and repeat the total check. SUM function.

If labels are wrong despite a correct total, compare the helper list against the matrix cell by cell. Keep each helper row’s asset, format, quantity, and cumulative boundary together. Do not independently rearrange a helper column.

Once the totals and combinations agree, use the two output columns as your item list. Keep the matrix and helper calculations available so that later quantity changes remain traceable to the repeated rows.

Sources