Excel functions / Dynamic arrays

VSTACK function

Stacks arrays on top of each other into one taller array.

Syntax

=VSTACK(array1, [array2], …)

Arguments

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

  1. Two lists, one under the other

    ABCDE
    1Team ATeam B
    2AnaDevAna
    3BenEveBen
    4Dev
    5Eve

    In E2: =VSTACK(A2:A3, C2:C3) → Ana; Ben; Dev; Eve

    Try this example

  2. One sorted list with no repeats

    ABCDE
    1Team ATeam B
    2BenAnaAna
    3AnaDevBen
    4Dev

    In E2: =SORT(UNIQUE(VSTACK(A2:A3, C2:C3))) → Ana; Ben; Dev

    Try this example

Common mistakes

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.

Translate a whole formula

Try VSTACK in the playground

Practise it

Related functions