Join two spill ranges to one range with two columns

Occasional Contributor

Hi community


I would like to join two spill ranges into one range with two columns but cannot figure out how to do it. What I would like is the new range below. I believe it is what HSTACK() does but I do not have access to O365 yet through my corporate Excel. 



Any solution to this?



12 Replies
best response confirmed by mlylyly (Occasional Contributor)


For this task you can also use the CHOOSE() function in older Excel versions.


In cases where your Range 1 = E4:E6 and Range 2 = G4:G6.

In older versions of Excel you have to finish this with CTRL + SHIFT + ENTER as an array formula.



If you're looking to join two spills, then you most likely have 365 but without HSTACK (Possibly on Semi-Annual channel).  This might work in your version.

Thanks, I used dscheikey's version, worked like a charm!





Both of your solutions work but I have to expand it to joining to arrays with multiple columns, do you have a solution for that? 

I tried a simple expansion of both of your formulas but either seem to work.

Thanks in advance!

P.s., yes I have 365 but without HSTACK due to semi-annual channel, you're right




You could do this:

Thanks but I am looking for a solution which would work in a let() function without helper spills
Where are the "helper spills" ?
Maybe I'm not following but if I have a formula with

=LET(arrray1,array2,output) I cannot refer to ranges across the two arrays e.g., A2:G4 and A2:G2 which covers both array1 and array2


Somewhat less elegant but playing with indices will work.

= LET(
      k, SEQUENCE(1,6),
      h, SEQUENCE(3),
      IF(k<4, array1#, INDEX(array2#,h,k-3))


Of course, provided you have Lambda, you could always provide some window dressing

= HStackλ(array1#, array2#)

= LAMBDA(a₁, a₂,
        k, SEQUENCE(1, COLUMNS(a₁) + COLUMNS(a₂)),
        h, SEQUENCE(MAX(ROWS(a₁), ROWS(a₂))),
        IF(k < 4, a₁, INDEX(a₂, h, k - 3))

Then again, you could make a case to corporate IT to put you on to the Insider beta channel so that you could preview new functionality on behalf of the company.



If your real data is also as simple as the example, I would do it that way:





Try this:

What I was looking for, thanks a lot!