Explanation

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

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

Example

Syntax

=HSTACK(array1,[array2],…)

array1 – The first array we want to stack. The first array we select will appear on the leftmost side of our newly created array.

[array2] – The second array we want to stack. It will appear to the right of 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 HSTACK:

• 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 rows, in the case of HSTACK):

 

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 rows, yet in the newly created array we require at least 3 rows – So Excel padded the extra row 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 HSTACK

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

And let’s try to see how we can use HSTACK with #N/A errors: