Dynamic arrays
VSTACK function: stack separate regional tables into one consolidated list
Append multiple ranges top-to-bottom into a single dynamic table for unified totals, sorting, or filtering with VSTACK.
Consolidate split tables without copy-pasting
Teams often maintain separate regional blocks or monthly logs with identical columns. VSTACK(array1, [array2], ...) appends each range vertically in order, creating a single live dataset that updates whenever any source block changes.
| NorthOrder (A) | NorthQty (B) | SouthOrder (D) | SouthQty (E) |
|---|---|---|---|
| N-101 | 120 | S-201 | 95 |
| N-102 | 180 | S-202 | 205 |
Count combined rows and total stacked quantities
Stacking North (A2:B3) above South (D2:E3) creates a 4-row, 2-column table. Choosing column 2 with CHOOSECOLS and summing yields 120 + 180 + 95 + 205 = 600.
=SUM(CHOOSECOLS(VSTACK(A2:B3, D2:E3), 2))Mismatched column widths pad extra cells with #N/A
If you stack a 1-column range onto a 2-column range—such as VSTACK(A2:B3, D2:D3)—the missing second column in the narrower block fills with #N/A.
=INDEX(VSTACK(A2:B3, D2:D3), 3, 2)Try the example
Use the original values or change them to test your understanding.