Dynamic Arrays
Append Ranges Horizontally & Vertically (HSTACK & VSTACK) While Dropping Blanks
Join side-by-side column blocks with HSTACK and append multi-region tables with VSTACK + FILTER so pre-allocated blank rows never leak zeros into consolidated reports.
Why modern HSTACK and VSTACK replace recursive index-stitching LAMBDAs
Before HSTACK and VSTACK arrived, appending Range1 (m x c1) beside Range2 (m x c2) required complex SEQUENCE + IF(col <= COLUMNS(Range1), INDEX(Range1,...), INDEX(Range2,...)) arrays. HSTACK(Range1, Range2) joins columns side-by-side directly, while VSTACK(Range1, Range2) appends rows top-to-bottom.
When source ranges include empty buffer rows reserved for future entries, wrapping VSTACK inside FILTER(VSTACK(A2:B4, D2:E4), VSTACK(A2:A4, D2:D4) <> "") strips blank rows automatically before counting or summing.
| Append Direction | Dynamic Array Formula | Output Shape | Key Benefit |
|---|---|---|---|
| Horizontal (Columns) | HSTACK(Block_Left, Block_Right) | Rows x (Cols1 + Cols2) | Side-by-side KPI block join |
| Vertical Raw | VSTACK(East_Rows, West_Rows) | (Rows1 + Rows2) x Cols | Includes blank buffer rows |
| Vertical Clean | FILTER(VSTACK(...), Keys <> "") | Active_Rows x Cols | Excludes empty buffer rows |
Clean Vertical Append Ignoring Blank Padding Rows
Stack both tables and filter on the stacked primary-key column being non-empty.
=ROWS(FILTER(VSTACK(A2:B4, D2:E4), VSTACK(A2:A4, D2:D4) <> ""))Horizontal Range Append with HSTACK
Join matching-height metric blocks side-by-side into a single wide array.
=COLUMNS(HSTACK(A2:B3, D2:E3))Try the example
Use the original values or change them to test your understanding.