VSTACK

VSTACK(array1 [,array2] [..])

Returns the array after putting multiple arrays on top of each other.

array1The first array to be appended.
array2(Optional) The second array to be appended.

REMARKS
* For an illustrated example refer to the page under Data Functions
* This function was added in Excel 2024.
* This function can create a Dynamic Array Formula.
* This function can be used to combine data from multiple worksheets.
* If the arrays are single rows, they are stacked on top of each other. Example 1.
* If the arrays are single columns, they are stacked in a single column. Example 2.
* If the arrays are not the same size, they are padded with #N/A.
* You can have a maximum of 254 arguments.
* You can use the CHOOSECOLS function to return the array with just a specific number of columns.
* You can use the CHOOSEROWS function to return the array with just a specific number of rows.
* You can use the HSTACK function to return the array after putting multiple arrays next to each other.
* You can use the TOCOL function to return the array transformed into a single column.
* You can use the TOROW function to return the array transformed into a single row.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

 ABCDE
1=VSTACK(B1:C1,B2:C2) = {1,2 ; 5,6}1234
2=VSTACK(B1:B2,C1:C2) = {1 ; 5 ; 2 ; 6}567 
3=VSTACK(B1:C1,B3:C3) = {1,2 ; 8,9}8910 
4=VSTACK(B1:C1,B2:C2,B3:C3) = {1,2 ; 5,6 ; 8,9}11   
5=VSTACK(B1:C1,C2:D3) = {1,2 ; 5,6 ; 8,9}    
6=VSTACK(B1:B3,C1:C3) = {1 ; 5 ; 8 ; 2 ; 6 ; 9}    
7=VSTACK(B1:B4,C1:C4) = {1 ; 5 ; 8 ; 11 ; 2 ; 6 ; 9 ; 0}    
8=VSTACK(B4,D3,E1) = {11 ; 10 ; 4}    
9=VSTACK(B1:C1,B2) = {1,2 ; 5,#N/A}    
10=VSTACK({"a","b"},C1:D1) = {"a","b" ; 1,2}    
11=VSTACK({"a" ; "b"},C1:D1) = {a,#N/A ; b,#N/A ; 1,2}    
12=VSTACK("'C:\temp\[Indirect-Test.xlsx]Sheet1'!$A$1") = 20    

1 - What is the combination of the range "B1:C1" and "B2:C2" as one array.
2 - What is the combination of the range "B1:B2" and "C1:C2" as one array.
3 - What is the combination of the range "B1:C1" and "B3:C3" as one array.
4 - What is the combination of the range "B1:C1", "B2:C2" and "B3:C3" as one array.
5 - What is the combination of the range "B1:C1" and "B2:C3" as one array.
6 - What is the combination of the range "B1:B3" and "C1:C3" as one array.
7 - What is the combination of the range "B1:B4" and "C1:C4" as one array.
8 - What is the combination of the range "B4", "D3" and "E1" as one array. These individual values are placed in a vertical array.
9 - What is the combination of the range "B1:C1" and "B2" as one array. Notice the second array is a different size so #N/A is used as padding.
10 - What is the combination of the array {"a","b"} and "C1:D1" as one array. Notice the comma in the array. This is interpreted as a 1x2 (1 row, 2 column) array the same size as "B1:C1".
11 - What is the combination of the array {"a" ; "b"} and "C1:D1" as one array. Notice the semi-colon in the array. This is interpreted as a 2x2 (2 row, 2 column) array a different size to "B1:C1".
12 - What is the value at the cell reference "A1", on worksheet "Sheet1", in the file "C:\temp\Indirect-Test.xlsx". The file does not have to be open.

© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top