How to use the HSTACK function
What is the HSTACK function?
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)
The HSTACK function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
1. HSTACK Function Syntax
HSTACK(array1,[array2],...)
2. HSTACK Function Arguments
| array1 | Required. The first cell range or array. |
| [array2] | Optional. The second cell range or array to merge. |
3. HSTACK Function example
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:
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}
4. HSTACK Function errors
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:
The IFNA function lets you remove #N/A errors.
Formula in cell E4:
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(value, value_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 "".
5. Extract unique distinct columns from multiple cell ranges
The image above shows a formula that extracts unique distinct columns from multiple cell ranges.
Formula in cell C10:
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}
Useful resources
HSTACK function - Microsoft support
How to combine ranges / arrays in Excel with VSTACK & HSTACK functions
'HSTACK' function examples
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
Functions in 'Array manipulation' category
The HSTACK function function is one of many functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form