Explanation

VSTACK is a function that allows you to stack 2 or more arrays vertically. An array could be a single cell, row, column, or any combination of rows and columns.

VSTACK is a new Excel function – It’s available for Excel 365 and Excel for the Web. 

Example

Syntax

=VSTACK(array1,[array2],…)

array1 – The first array we want to stack. The first array we select will appear at the top of our newly created array.

[array2] – The second array we want to stack. It will appear below the first array. We can stack additional arrays by adding more commas (,) followed by the arrays we want to stack. 

Important stuff to know about VSTACK:

• The arrays we stack do not have to be the same size. However, the newly created array will be in the size of the larger array (the one that has more columns, in the case of VSTACK):

 

As we can see, the above example results in an #N/A error in one of the cells. The #N/A appears as the first array had only 2 columns, yet in the newly created array we require at least 3 columns – So Excel padded the extra column with an #N/A.

As #N/A can be quite ugly, we are more than happy to get rid of it using the IFERROR (or IFNA) function. 

Let’s see how we use IFERROR to replace the #N/A errors with something a bit more pleasant to the eye, such as “nothing”/empty cell (which is represented by two quotes in Excel):

 

• If some of the arrays we stack have one or more empty cells, they will be represented as 0 (zero) in the newly created array:

Practice VSTACK

Let’s put our knowledge into practice and try the VSTACK function!

 

Now, let’s try another VSTACK question, this one – a bit more complicated 😉