Description
Use this function to vertically stack multiple ranges or arrays into a single range.
Syntax
VSTACK(array_1, [array_2], …, [array_29])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
| array_1 | Yes | The first range or array to vertically stack (join). | A cell reference, a cell range, or a formula which results in either of these. |
[array_2], …, [array_29] |
No | Additional ranges or arrays to vertically stack. Up to 29 arrays can be added. | A cell reference, a cell range, or a formula which results in either of these. |
Examples
Sample data
Data set 1
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Sales | Region | Commission |
| 2 | Q1 | |||
| 3 | John | 45000 | North | 4500 |
| 4 | Alice | 52000 | South | 5200 |
| 5 | Bob | 49500 | West | 4500 |
| 6 | Q2 | |||
| 7 | John | 48000 | North | 4800 |
| 8 | Alice | 55000 | South | 5500 |
| 9 | Bob | 51250 | West | 5100 |
Data set 2
Sheet 1 (Named "Employees_Q1")
| Name | Age | City | Salary |
|---|---|---|---|
| John | 35 | New York | 75000 |
| Alice | 28 | Chicago | 65000 |
| Bob | 42 | Los Angeles | 85000 |
Sheet 2 (Named "Employees_Q2")
| Name | Age | City | Salary |
|---|---|---|---|
| George | 22 | Denver | 55000 |
| Helen | 30 | Scottsdale | 71000 |
Sample formulas
| Use case | Formula | Result | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Join two horizontal arrays into a single table. |
=VSTACK(A2:D4, A6:D8)
|
This formula reads the two ranges on the tab and combines the Q1 and Q2 data into a single range. Using data set 2, this produces the following:
|
||||||||||||||||||||||||
| Combine data from two separate sheets into one table. |
=VSTACK(Employees_Q1!A2:D4, Employees_Q2!A2:D3)
|
This formula extracts the Q1 employee data from Sheet
1 (
|
||||||||||||||||||||||||
|
Combine data from one table with a filtered set of data from a second table. A use case for combining data from one table with a filtered set of data from a second table is when you want to enrich data in your primary table with specific information from a related table, but only for certain entries. |
=VSTACK(A2:D4, FILTER(A6:D8, B6:B850000))
|
Combines Q1 data with filtered Q2 data (where Sales are greater than 50000). Using data set 1, this produces the following:
|
||||||||||||||||||||||||
| Combine data from two separate ranges and then extract only the unique rows. |
=UNIQUE(VSTACK(A2:D4, A6:D8))
|
Combines Q1 and Q2 data and automatically sums the values for rows that have identical column 1 entries. Using data set 2, this produces the following:
Note:UNIQUE automatically sums numeric values when it finds duplicate entries in the first column. |
Notes
- As with all array functions, VSTACK dynamically populates results across adjacent cells, starting from the cell containing the VSTACK function.
-
VSTACK:
- Combines multiple ranges from one or more sheets vertically into a single vertical range. The order of the arrays determines the order of stacking. The first array in the function will always be on the top.
- Can stack ranges that have different widths.
- Is useful for combining data from multiple time periods or sources.
- Is useful for appending new records without manual reformatting.
- Keeps data dynamic (if new rows are added, the output updates).
Tips
- If you have a header row and don't want the headers to be included, start your array values one row down (for example in the above data set, start with A2 rather than A1).
- VSTACK can be combined with other functions, including:
Array formula functions
Here are the other array formula functions supported in Workiva spreadsheets.