The VLOOKUP Limitation
For years, Excel users have relied on VLOOKUP or INDEX/MATCH to combine data from different places. However, those functions are designed to pull individual data points based on a specific matching criteria.
What if you don’t want to match anything? What if you simply have a list of “January Sales” in one column, and a list of “February Sales” in another, and you just want to stack them side-by-side into a single, cohesive table so you can generate a chart?
Historically, you had to manually copy and paste the columns next to each other. If the original data changed, you had to copy and paste again. With the introduction of Dynamic Arrays, Microsoft solved this problem by releasing a dedicated function: HSTACK (Horizontal Stack).
Step 1: The Basic Syntax
The HSTACK function is incredibly intuitive. It simply takes one array (a block of cells), grabs the next array you specify, and glues them together horizontally (left-to-right).
The syntax is:
=HSTACK(array1, [array2], ...)
Let’s say you have a list of Employee Names in A2:A10, and a list of their corresponding Salaries in D2:D10. Because these are in different parts of the spreadsheet, you want to bring them together into a clean, two-column table starting in cell G2.
- Click cell
G2. - Type:
=HSTACK(A2:A10, D2:D10) - Press Enter.
The names will instantly appear in column G, and the salaries will appear in column H. Because this is a dynamic array, if you change a name in column A, the HSTACK output in column G will instantly update.
Step 2: Combining Disparate Data Sources
The true power of HSTACK is that the arrays do not need to come from the same worksheet.
Imagine you have three different worksheets: “East Coast”, “West Coast”, and “Central”. Each sheet has a single column listing the top 5 performing salespeople for that region.
You want to create a master dashboard that shows all three lists side-by-side.
In your Dashboard sheet, you can type:
=HSTACK('East Coast'!A2:A6, 'Central'!A2:A6, 'West Coast'!A2:A6)
This single formula will instantly generate a 3-column, 5-row table pulling the live data from across your entire workbook. No copying, no pasting, and no complex lookups.
Step 3: Stacking Single Values and Arrays
You are not limited to just stacking existing cell ranges. You can stack text strings or numbers directly inside the formula alongside your arrays. This is incredibly useful for adding dynamic headers to your stacked data.
Building on the previous example, what if those region sheets didn’t have headers? You can create the headers directly inside a vertical array VSTACK, and combine it with your HSTACK.
=VSTACK({"East", "Central", "West"}, HSTACK('East Coast'!A2:A6, 'Central'!A2:A6, 'West Coast'!A2:A6))
This nested formula first creates a row containing the words “East”, “Central”, and “West”, and then stacks the three columns of names directly underneath them, building a fully formatted, dynamic table from scratch in a single cell.
Step 4: Handling Mismatched Array Sizes
There is one common error you will encounter with HSTACK. What happens if the arrays are different lengths?
If you try to stack a 10-row array next to a 5-row array:
=HSTACK(A1:A10, C1:C5)
Excel will successfully stack them side-by-side. However, because the second column runs out of data halfway down, Excel will fill the bottom 5 rows of that column with ugly #N/A errors to maintain the rectangular shape of the array.
To fix this, wrap the HSTACK function inside an IFNA function to replace those errors with a clean blank space (""):
=IFNA(HSTACK(A1:A10, C1:C5), "")
This combination ensures your dynamic reports remain visually flawless, even when your data sources are asymmetrical.