Excel FILTER + HSTACK Formula Explained

A practical guide for Quantity Take-Off (QTO) reports using dynamic arrays in Excel.

This formula uses FILTER + HSTACK to extract selected columns from the Volume QTO worksheet and display only rows matching cell F4.

1. Original formula

=FILTER(
    HSTACK(
        'Volume QTO'!F$33:F$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!B$33:B$1031,
        'Volume QTO'!J$33:J$1031,
        'Volume QTO'!O$33:O$1031,
        'Volume QTO'!T$33:T$1031,
        'Volume QTO'!Y$33:Y$1031,
        'Volume QTO'!AD$33:AD$1031,
        'Volume QTO'!AI$33:AI$1031
    ),
    'Volume QTO'!C$33:C$1031=F4,
    ""
)

2. Three main components

⇥

HSTACK — Combine columns horizontally

Combines 11 selected source columns into a temporary table, in the specified order.

▽

FILTER — Select matching rows

Returns rows where column C of Volume QTO equals the value in F4.

“”

Empty result — No matches

The final "" displays an empty string when no matching rows are found.

3. HSTACK output order

Output column	Source column	Source range
1	F	F33:F1031
2	A	A33:A1031
3	A	A33:A1031
4	A	A33:A1031
5	B	B33:B1031
6	J	J33:J1031
7	O	O33:O1031
8	T	T33:T1031
9	Y	Y33:Y1031
10	AD	AD33:AD1031
11	AI	AI33:AI1031

Note: Column A appears three times, so output columns 2, 3, and 4 contain identical values. This may be intentional for your QTO layout.

4. Interactive FILTER example

Imagine that F4 contains Concrete. Select a different criterion below to see which rows would appear.

Choose F4 value

Source row	Column F	Column J
33	Pile Cap	2.50
35	Column	3.20
37	Beam	4.50

Showing 3 matching rows for Concrete. The actual formula returns 11 columns.

The filter condition

'Volume QTO'!C$33:C$1031=F4

Excel compares every cell in C33:C1031 with F4 and keeps only the corresponding rows that match.

5. What the $ symbols mean

'Volume QTO'!F$33:F$1031

• F: Relative column; can change if the formula is copied horizontally.
• $33: Fixed starting row.
• $1031: Fixed ending row.
• F4: Fully relative reference; can shift when copied across or down.

6. Recommended formula improvements

Option A — LET for easier maintenance

=LET(
    Data,HSTACK(
        'Volume QTO'!F$33:F$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!A$33:A$1031,
        'Volume QTO'!B$33:B$1031,
        'Volume QTO'!J$33:J$1031,
        'Volume QTO'!O$33:O$1031,
        'Volume QTO'!T$33:T$1031,
        'Volume QTO'!Y$33:Y$1031,
        'Volume QTO'!AD$33:AD$1031,
        'Volume QTO'!AI$33:AI$1031
    ),
    Criteria,'Volume QTO'!C$33:C$1031,
    FILTER(Data,Criteria=F4,"")
)

LET gives names to the temporary table and criterion range. It improves readability but does not guarantee faster calculation.

Option B — Skip calculation when F4 is empty

=IF(F4="","",FILTER(
HSTACK(
'Volume QTO'!F$33:F$1031,
'Volume QTO'!A$33:A$1031,
'Volume QTO'!A$33:A$1031,
'Volume QTO'!A$33:A$1031,
'Volume QTO'!B$33:B$1031,
'Volume QTO'!J$33:J$1031,
'Volume QTO'!O$33:O$1031,
'Volume QTO'!T$33:T$1031,
'Volume QTO'!Y$33:Y$1031,
'Volume QTO'!AD$33:AD$1031,
'Volume QTO'!AI$33:AI$1031),
'Volume QTO'!C$33:C$1031=F4,""))

This prevents blank F4 from matching blank cells in the source criteria column.

7. Important limitations

• Inserted columns: Excel usually adjusts references when columns are inserted, but the formula does not identify fields by header name.
• New rows: Rows beyond 1031 are not included unless the referenced range expands.
• Blank source cells: Blank values in the returned array may display as zeros.
• Spill range: The output needs 11 empty columns and enough free rows, otherwise #SPILL! can occur.
• Compatibility: Requires an Excel version supporting both FILTER and HSTACK, such as Microsoft 365 or Excel 2024.

Recommendation: Keep FILTER + HSTACK when you need to select and reorder QTO fields. Use LET for readability and the F4 blank check for cleaner reports. For layouts where columns are frequently rearranged, consider header-based XMATCH + CHOOSECOLS.

✎ ↑ Top