VSTACK

New Excel function in Excel Office 365

This is one of the new dynamic array functions that can return arrays. It works by joining arrays vertically. There is a similar function called HSTACK for joining arrays horizontally.

Syntax

VSTACK(array1, [array2],…)

The array can be hard coded, a range or the results of a formula.

All [array] values after the first value are optional.

Comments

The arrays do not have to be the same size, missing values are replaced with #N/A values.

Don’t include the headings in each array, otherwise they will be repeated through the joined data.

Best to use

• Joining multiple sources of data into a single array

Limitations

• Blank values are replaced with zeros.

Tip

If the arrays being joined are different sizes, use the IFNA function to suppress the #N/A values.

To suppress empty values in arrays that would usually return a zero, use the SUBSTITUTE function.

Examples

Example - VSTACK ranges from the same worksheet

In the example, three ranges from the same worksheet are stacked vertically to form a single range ‘E5:G13’.

The headings have been manually entered.

Example - VSTACK with dynamic headings

In the example, three ranges from the same worksheet are stacked vertically to form a single range ‘E4:G13’.

The headings are created by being included as the first array in the VSTACK function, ‘E5:G5’.

Example - VSTACK different sized ranges

In the example, three ranges from the same worksheet are stacked vertically to form a single range ‘E4:G13’. The first array has three columns, the second array, two columns and the third array, one column.

The missing data is replaced by #N/A values.

The #N/A values can be replaced by wrapping the formula in a IFNA function.
Alternately they can be removed by using an IFERROR function.
Given the choice, always use IFNA as it is more specific.

Example - VSTACK with missing data

In the example, three ranges from the same worksheet are stacked vertically to form a single range ‘E4:G13’. However some of the ranges have missing data.
In these cases, VSTACK returns a zero instead.

The SUBSTITUTE function can be used to replace the returned zeros with an empty string. Note that the SUBSTITUTE function is called with no value ,,.
This is to ensure that real zeros in the data are not replaced with an empty string.

The downside with this approach is that numbers are returned as text.

Example - VSTACK with only selected columns

In the example, three ranges from the same worksheet are stacked vertically to form a single range ‘E4:F13’ but only the first and last columns are returned.

There are two approached, the first is to use the CHOOSECOLS function to select columns from a range.
The second option is to use the HSTACK function to horizontally join ranges before vertically stacking them.

Each option is an equally good approach.

Example - VSTACK from different worksheets

In the example, three ranges, each from a different worksheet are stacked vertically to form a single range ‘E4:G13’.

All that really changes here is the reference to the worksheet.

Example - VSTACK from a dynamic range of worksheets

In the example, ranges from all worksheets from a named first to a named last worksheet are stacked vertically.
Because we are unsure of the exact ranges, the target range is expanded to include 10 rows. In some cases, this will return blank rows. The FILTER function is used to filter out cells with no data based on the first column of data.

There is a weakness to this approach. Rows might be excluded if the first column has no data.
This also works best if there is an identifier in the data that identifies which sheet it is coming from.