Zoho Sheet HSTACK: Combine Columns and Check Record Alignment
Combine arrays horizontally in Zoho Sheet with complete spill examples, matching-ID controls and a clear warning about positional mismatches.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
=HSTACK(A2:B5;D2:E5) appends D2:E5 to the right of A2:B5. Zoho Sheet HSTACK puts arrays side by side, preserving their supplied row positions. It does not look up a matching ID or repair two lists that are in different orders.
Availability and version history
- Introduced: Named in the seven-function launch linked from Zoho’s November 2022 release history. No numbered cloud release is given.
- Availability: Documented in Zoho Sheet’s current rolling web-service help; no final supported-version endpoint is established.
- Scope: Mobile, offline and imported-workbook parity remain separately unverified.
- Checked: October 14, 2026, against the official HSTACK entry. Coordinate, type and arithmetic examples were independently calculated; no native Zoho Sheet execution was performed.
Syntax and the direction of combination
HSTACK(array1; [array2]; ...)
The first array is required; further arrays are optional. Each additional array goes to the right of the previous one. The official reference illustrates two equal-height ranges. All examples below also use equal heights, keeping dimensional errors separate from record-alignment problems.
VSTACK appends arrays below one another instead. The two functions have different shape contracts and are not aliases.
Combine a quantity list with its aligned price list
Create AlignedLists, reserving A1:N13. Enter the complete two input tables below. IDs are text; Units and Unit price are numeric. Leave column C empty as a visual separator.
| Row | A: Quantity ID | B: Units | D: Price ID | E: Unit price |
|---|---|---|---|---|
| 2 | P1 | 3 | P1 | 8 |
| 3 | P2 | 5 | P2 | 7 |
| 4 | P3 | 4 | P3 | 11 |
| 5 | P4 | 6 | P4 | 9 |
Put the four input field names in A1,B1,D1,E1. Leave G2:J5 empty and enter =HSTACK(A2:B5;D2:E5) in G2.
| Output row | G: Quantity ID | H: Units | I: Price ID | J: Unit price |
|---|---|---|---|---|
| 2 | P1 | 3 | P1 | 8 |
| 3 | P2 | 5 | P2 | 7 |
| 4 | P3 | 4 | P3 | 11 |
| 5 | P4 | 6 | P4 | 9 |
Zoho’s dynamic-array instructions specify entering one formula in its anchor and pressing Enter. Leave the entire intended result rectangle empty. Do not fill duplicate formulas across it; an obstructing value produces #SPILL!. Edit source cells or the anchor rather than treating the returned cells as independent inputs.
Enter K2 =EXACT(G2;I2) and L2 =H2*J2, then fill K2:L2 through row 5. K is TRUE throughout. The amounts in L are 24,35,44,54; N2 =SUM(L2:L5) is 157. Keeping both IDs makes the assumed pairing visible before multiplying quantity and price.
EXACT is case-sensitive. Its TRUE results verify the explicit text pairs, not uniqueness, currency, price validity or whether the lists came from the same reporting date.
Argument order changes the field order
Leave G8:J11 empty and enter =HSTACK(D2:E5;A2:B5) in G8. The first two rows are P1,8,P1,3 and P2,7,P2,5; the remaining rows are P3,11,P3,4 and P4,9,P4,6.
The price pair now comes before the quantity pair. No source values were sorted or matched. A later formula must use the new field positions deliberately rather than inheriting labels from a different arrangement.
Copy AlignedLists as ChangedQuantity and change only B3 from 5 to 10. H3 and J9 become 10 in the two views; L3 becomes 70 and N2 becomes 192. All ID comparisons remain TRUE. The returned arrays stay linked to their inputs.
Advanced: demonstrate a positional mismatch safely
Duplicate the original AlignedLists as MisalignedPrices. Replace D2:E3 with P2,7 and P1,8, leaving A:B and the last two price records unchanged. G2:J5 now begins P1,3,P2,7 / P2,5,P1,8.
K2:K5 becomes FALSE,FALSE,TRUE,TRUE. The unguarded amounts in L become 21,40,44,54, and N2 becomes 159. That plausible total is wrong for the intended ID pairing: HSTACK has combined positions rather than performing a key-based join.
On this copied sheet, reserve M2:M5 for guarded results. Enter M2 =IF(K2;H2*J2;"ID mismatch") and fill through M5. The results are ID mismatch, ID mismatch, 44, 54. Put =COUNT(M2:M5) in N3 for 2, and =IF(N3=4;"Complete numeric rows";"Incomplete") in N4 for Incomplete.
COUNT excludes the two text messages under its documented rule. Do not present the surviving amounts as a complete four-record total. IF flags the problem without silently guessing how to reorder the source. Align the records through a separately verified matching method before using the combined numeric fields.
Combine three explicitly aligned blocks
Create ThreeBlocks, reserving A1:M8. Enter the original quantity table in A1:B5 and original price table in D1:E5. In G1:H1 put Category ID and Category. Enter P1,Office / P2,Field / P3,Office / P4,Field in G2:H5.
Leave J2:M5 empty and enter =HSTACK(A2:B5;E2:E5;H2:H5) in J2. Expected rows are:
- P1,3,8,Office
- P2,5,7,Field
- P3,4,11,Office
- P4,6,9,Field
The result has four rows and four columns: widths 2+1+1. This formula deliberately omits the repeated price/category ID fields because this stated fixture is already aligned. Retain those source IDs and verify alignment before applying that compact layout to other data.
Unequal heights and other boundaries
The HSTACK help lists #N/A! when one or more input arrays has fewer rows than the maximum row count. The short entry does not describe the exact affected-cell layout, so this guide does not promise padding locations or a whole-result error for unequal heights.
It also lists generic invalid-type, name and reference errors. Blank or error inputs, imported formula arrays, very large dimensions and every client’s display remain unverified. Equal row counts alone are not enough: the MisalignedPrices example has valid dimensions but incorrect record pairing. SUM can add the resulting numbers without detecting that semantic mistake.