# 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.

What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]

### Functions in 'Array manipulation' category

The HSTACK function function is one of 11 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 signsUse 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 OscarYou can contact me through this contact form