In Microsoft 365 for Mac or Excel 2024 for Mac, combine CHOOSECOLS, TOCOL, HSTACK, and FILTER to turn repeated art-asset labels and quantities into inventory rows. The worked formula below preserves pair alignment, zero quantities, and incomplete entries.

This walkthrough is for a Mac desktop user whose worksheet repeats an attribute column followed by its quantity column. Here, each attribute is an art-asset label, such as Circle Tile. The task is to produce a two-column inventory, then look up quantities without separating them from their labels.

Which Excel for Mac versions support this formula?

Microsoft explicitly lists Excel for Microsoft 365 for Mac and Excel 2024 for Mac for all three reshape functions: CHOOSECOLS, TOCOL, and HSTACK. Use one of those editions for the main formula.

Excel 2021 for Mac includes dynamic arrays, FILTER, LET, and XLOOKUP, but that does not establish support for the newer reshape functions. Microsoft’s Excel 2021 for Mac feature documentation identifies its available array and lookup capabilities; the three function pages above do not list Excel 2021.

Check your edition and version in the application’s About dialog. Microsoft’s Office version guide explains that the Mac dialog shows both version and license type. If your edition lacks the reshape functions, use Microsoft 365 for Mac or Excel 2024 for Mac for the automatic method, or use the manual stacking path described below.

Set up a complete synthetic example

On a blank worksheet, enter this data in A1:F4. Leave the two empty cells in the middle of row 3 genuinely empty. All labels and quantities are illustrative.

Attribute 1 Quantity 1 Attribute 2 Quantity 2 Attribute 3 Quantity 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Each adjacent pair represents one inventory entry. The intended output reads across each source row before continuing downward. Circle Tile appears twice because these are separate entries; combining repeated labels would be an additional task.

This method assumes every label is immediately followed by its quantity and all selected columns belong to that repeating pattern. If your sheet also contains a collection name or another field that must accompany every entry, this two-column output does not retain it. Decide which fields the inventory needs before applying the formula.

Turn the repeated pairs into inventory rows

Enter Asset in H1 and Quantity in I1. Keep the output area below those headings empty. Paste this complete formula into H2, then press Return. Dynamic array formulas are entered once and expand into neighboring cells, as described in Microsoft’s array formula guide.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

The expected result occupies H2:I9:

Asset Quantity
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

The example’s pair order, eight output entries, zero preservation, and total quantity of 28 were checked with a small deterministic Python calculation. The Excel formula was inspected against Microsoft’s documented function syntax; it was not executed in Excel.

How the formula keeps the pairs aligned

LET gives intermediate results names so the formula can reuse them. The names source, clean, labels, quantities, and keep exist within this formula; you do not need to create named ranges. Microsoft documents this behavior in its LET reference.

CHOOSECOLS selects label columns 1, 3, and 5 and quantity columns 2, 4, and 6. These numbers are positions inside the selected source range. Microsoft’s CHOOSECOLS documentation confirms that it returns the specified columns from an array.

Both TOCOL calls use 0 to retain every position and FALSE to scan across rows. Because they traverse matching positions in the same order, each label stays aligned with its quantity. Microsoft documents both arguments in the TOCOL reference.

HSTACK places the two resulting columns beside each other. Both contain nine positions before filtering, so their lengths match. Microsoft explains that HSTACK appends arrays horizontally and pads shorter arrays with errors when their row counts differ.

Finally, keep selects positions containing a label, a quantity, or both. FILTER removes only completely empty pairs. Its final argument supplies a message when no entries remain, following Microsoft’s documented FILTER syntax.

Preserve blanks and zeroes without hiding incomplete entries

A blank quantity and a numeric zero carry different information. Zero is an entered count; a blank still needs interpretation. Microsoft’s TOCOL examples show that untreated blank cells can return zero. The clean step therefore uses IF to return empty text for empty source cells before reshaping. Microsoft documents conditional return values in its IF reference.

The formula deliberately keeps incomplete pairs visible. Use these checks before relying on the result:

Source pair Output behavior Action
Label and numeric quantity Keeps both Check that the quantity is intended
Label and zero Keeps zero Retain when zero is an intentional count
Both cells empty Removes the pair No correction needed
Label with empty quantity Keeps the label and empty quantity Complete the quantity
Empty label with quantity Keeps the empty label and quantity Complete the label

Avoid independently dropping blanks from the label and quantity arrays. If a label is missing in one position and a quantity is missing elsewhere, removing blanks separately can shift the arrays and create incorrect pairings. Retaining positions until after joining is the key design choice in this formula.

An apparently empty cell containing spaces is not treated as empty by this formula. Resolve such entries in the source. Likewise, the formula preserves quantities entered as text rather than converting them automatically. Review those values before calculating totals.

Look up one entry or return every matching entry

Enter Circle Tile in K2. To retrieve its first quantity, enter this formula in L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# refers to the entire spilled result beginning at H2, so the lookup follows changes in its size. Microsoft explains this notation in the spilled range operator documentation.

The result is 4. The explicit match mode 0 requests an exact match, and the default search returns the first matching entry. Microsoft’s XLOOKUP reference documents both behaviors. It also confirms that XLOOKUP is unavailable in Excel 2016 and Excel 2019.

If repeated labels are meaningful, a first-match lookup is insufficient. To return every Circle Tile entry, place this complete alternative formula in N2, leaving room in columns N:O:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

This returns two rows: Circle Tile with 4 and Circle Tile with 7. FILTER returns rows satisfying the supplied condition, as documented in Microsoft’s FILTER reference. Choose the first-match formula when one matching entry answers your question; choose the filtered result when you need to inspect every occurrence.

Verify the result and adapt the source range

For the sample, confirm eight output rows and check that Dot Pattern still has quantity zero. Then compare the source quantity total with the reshaped quantity total. Enter these formulas in two spare cells:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Both should return 28. Microsoft’s SUM documentation supports adding multiple ranges and explains that text values are ignored. Matching totals therefore do not prove that every quantity is numeric, and they cannot detect quantities swapped between labels. Check the label–quantity pairs as well.

For more source rows, change A2:F4 in the main formula to the actual rectangle, such as A2:F100. Rows outside that reference are not included. For four repeated pairs, change the source to eight columns, such as A2:H100, and update the label positions to 1,3,5,7 and quantity positions to 2,4,6,8.

When expanding to eight source columns, move the output to a clear area, such as J2, and update downstream spill references to J2#. Keep source and output separate. An invalid column position produces an error, as documented under CHOOSECOLS errors.

Resolve blocked output or use the manual stacking path

If the main formula returns #SPILL!, select the formula cell and inspect the intended output area. Move existing entries out of that area and check for merged cells. Place the formula outside any Excel Table: Microsoft documents that spilled array formulas cannot spill within tables or into merged cells in its #SPILL! troubleshooting guide.

If the source contains an error, correct that source entry before using the inventory. The formula does not suppress errors; Microsoft notes that an error in FILTER’s inclusion array causes the function to return an error. Wrapping the entire transformation in error suppression could conceal an entry requiring correction.

For a small worksheet in Excel 2021 for Mac, you can reach the same two-column structure manually: copy A2:B4 to H2:I4, copy C2:D4 to H5:I7, and copy E2:F4 to H8:I10. Remove only rows where both cells are empty. This stacks entries by pair block rather than the main formula’s source-row order, and requires repeating the work after source changes.

After that manual setup, Excel 2021 for Mac can use its documented XLOOKUP capability with fixed ranges:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

A useful finished inventory preserves every meaningful pair, exposes incomplete entries, and uses a lookup suited to repeated labels. Confirm those properties with the sample first, then expand the source range to fit your worksheet.

Sources