Description
Use this function to horizontally stack multiple ranges or arrays into a single range.
Syntax
HSTACK(array_1, [array_2], …,[array_29])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array_1 |
Yes | The first range or array to horizontally stack. | A cell, a cell range, or a formula which results in either of these. |
[array_2], …, [array_29] |
No | Additional ranges or arrays to horizontally stack. Up to 29 arrays can be added. | A cell reference, a range, or a formula which results in either of these. |
Examples
Sample data
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Age | Salary | Title | City | Years |
| 2 | John | 35 | 75000 | Pilot | New York | 9 |
| 3 | Alice | 28 | 65000 | Medic | Chicago | 2 |
| 4 | Bob | 42 | 85000 | Manager | Los Angeles | 5 |
| 5 | George | 22 | 55000 | Attendant | Atlanta | 1 |
| 6 | Helen | 30 | 71000 | Jr. Pilot | New York | 8 |
Sample formulas
Ranges must match! If you are using multiple ranges with FILTER, the row values must be the same for all ranges specified as part of the filter expression.
| Use Case | Formula | Result | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Create a data set listing only Name, Title, and City. | =HSTACK(A1:A6,D1:E6) |
This formula extracts the Name (A), Title (D) and City (E) columns into a new set, along with the column heads.
|
||||||||||||||||||
| Have a text string for an error if there is a mismatch in the formula. | =IFERROR(HSTACK(A1:A4, {"City"}), "Invalid") |
This formula uses IFERROR to handle mismatched array sizes gracefully. For this data set it would return the following:
|
Notes
- As with all array functions, HSTACK dynamically populates results across adjacent cells, starting from the cell containing the HSTACK function.
- HSTACK:
- Combines multiple ranges horizontally into a single range.
- Can stack ranges with different heights.
- Is useful for combining data from multiple columns or sources.
Tips
- If you have header row(s), start your array values one row down (for example in the above data set, start with A2 rather than A1) if you don't want the headers to be included.
- The order of the arrays determines the order of stacking. The first array in the function will always be at the left.
- HSTACK can be effectively combined with several other functions, including:
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.