Excel functions / Dynamic arrays
VSTACK function
Stacks arrays on top of each other into one taller array.
Syntax
=VSTACK(array1, [array2], …)
Arguments
array: The arrays to stack, top to bottom.
When to use it
VSTACK puts ranges one under another and gives back a single list. It is the simple way to combine lists that live in different places, and the result can go straight into SORT, UNIQUE or FILTER.
HSTACK does the same side by side.
Examples
Two lists, one under the other
A B C D E 1 Team A Team B 2 Ana Dev Ana 3 Ben Eve Ben 4 Dev 5 Eve In E2:
=VSTACK(A2:A3, C2:C3)→ Ana; Ben; Dev; EveOne sorted list with no repeats
A B C D E 1 Team A Team B 2 Ben Ana Ana 3 Ana Dev Ben 4 Dev In E2:
=SORT(UNIQUE(VSTACK(A2:A3, C2:C3)))→ Ana; Ben; Dev
Common mistakes
- #N/A at the side. If one range is wider than another, the narrower one is padded with #N/A. Wrap the formula in IFNA to show something else:
=IFNA(VSTACK(A2:B3, D2:D3), ""). - #SPILL! The result needs empty cells below it. Clear whatever is in the way.
- Empty cells turning into 0. An empty cell inside a stacked range shows as 0. To leave those out, wrap the result in FILTER.
Common questions
How do I combine two columns into one in Excel?
Use =VSTACK(A2:A10, C2:C10) to put one list under the other. Wrap it in UNIQUE to drop repeats, or in SORT to put the result in order.
VSTACK in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: SROVNAT.SVISLE
- Danish: VSTAK
- Dutch: VERT.STAPELEN
- Finnish: VPINO
- French: ASSEMB.V
- German: VSTAPELN
- Hungarian: FÜGG.HALMOZÁS
- Italian: STACK.VERT
- Norwegian: VSTAKK
- Polish: STOS.PION
- Portuguese (Portugal): JUNTARV
- Russian: ВСТОЛБИК
- Spanish: APILARV
- Swedish: VSTACK
- Turkish: DÜŞEYYIĞ