Author: Oscar Cronquist Article last updated on May 05, 2022

HSTACK function example

The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Formula in cell E3:

=HSTACK(B2:D4, F2:H:4)

The HSTACK function is available to Excel 365 users and is in the "Array manipulation" category.

1. HSTACK Function Syntax

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

Back to top

2. HSTACK Function Arguments

array1 Required. The first cell range or array.
[array2] Optional. The second cell range or array to merge.

Back to top

3. HSTACK Function example

HSTACK function example1

The image above demonstrates how the HSTACK function merges the ranges B2:D4 (blue) and F2:H4 (red). It appends the second cell range (red) to the right of the first cell range (blue).

Formula in cell E4:

=HSTACK(B2:D4, F2:H4)
Note, if any of the values in the source ranges change the same values in the result from the HSTACK function change as well.

3.1 Explaining formula

Step 1 - HSTACK function

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

Step 2 - Populate arguments

array1 - B2:D4
[array2] - F2:H4

Step 3 - Evaluate function

HSTACK(B2:D4, F2:H4)

becomes

HSTACK({89, 68, 19;27, 84, 92;26, 98, 62}, {37, 89, 99;63, 8, 1;100, 31, 70})

and returns

{89, 68, 19, 37, 89, 99;
27, 84, 92, 63, 8, 1;
26, 98, 62, 100, 31, 70}

Back to top

4. HSTACK Function errors

HSTACK function NA errors1

The image above demonstrates what happens when you try to append two cell ranges with different numbers of rows. The first cell range (blue) has three columns and the second cell range (red) has 2 columns.

The result is an array containing #N/A errors in locations where no value exists.

Formula in cell E4:

=HSTACK(B2:D4, F2:H3)

HSTACK function IFNA function

The IFNA function lets you remove #N/A errors.

Formula in cell E4:

=IFNA(HSTACK(B2:D4, F2:H3),"")

4.1 Explaining formula

Step 1 - Stack cell ranges horizontally

HSTACK(B2:D4, F2:H3)

becomes

HSTACK({89, 68, 19;
27, 84, 92;
26, 98, 62},
{37, 89, 99;
63, 8, 1}
)

and returns

{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,#N/A,#N/A,#N/A}

Step 2 - Remove #N/A errors

The IFNA function catches #N/A errors and replaces them with a value you provide.

IFNA(valuevalue_if_na)

IFNA(HSTACK(B2:D4, F2:G:4), "")

becomes

IFNA(
{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,#N/A,#N/A,#N/A}
, "")

and returns

{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,"","",""}

Note that the IFNA function replaces the #N/A errors with an empty string "".

Back to top

5. Extract unique distinct columns from multiple cell ranges

HSTACK function Unique distinct columns

The image above shows a formula that extracts unique distinct columns from multiple cell ranges.

Formula in cell C10:

=UNIQUE(HSTACK(B3:D5, F3:H5), TRUE)

Explaining formula

Step 1 - Join cell ranges

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

HSTACK(B3:D5, F3:H5)

becomes

HSTACK({89, 27, 26;
"Charles", "Tina", "Linda";
19, 45, 62},
{89, 27, 26;
"Charles", "Tina", "Laura";
24, 45, 62})

and returns

{89,27,26,89,27,26;
"Charles","Tina","Linda","Charles","Tina","Laura";
19,45,62,24,45,62}.

Bolded values are two duplicate columns.

Step 2 - Extract unique distinct columns

The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.

UNIQUE(array, [by_col], [exactly_once])

UNIQUE(HSTACK(B3:D5, F3:H5), TRUE)

becomes

UNIQUE({89,27,26,89,27,26;
"Charles","Tina","Linda","Charles","Tina","Laura";
19,45,62,24,45,62}, TRUE)

and returns

{89,27,26,89,26;
"Charles","Tina","Linda","Charles","Laura";
19,45,62,24,62}

Back to top