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?Thanks!

mlylyly For this task you can also use the CHOOSE() function in older Excel versions.=CHOOSE({1,2},E4:E6,G4:G6)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.

Join two spill ranges to one range with two columns

12 Replies

mlylyly
Copper Contributor
Nov 16, 2022
Thanks, I used dscheikey's version, worked like a charm!
Patrick2788
Silver Contributor
Nov 14, 2022
mlylyly
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.
=IF({1,0},A1#,C1#)
- mlylyly
  Copper Contributor
  Nov 18, 2022
  Patrick2788
  
  dscheikey
  
  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
  - dscheikey
    Bronze Contributor
    Nov 18, 2022
    mlylyly
    If your real data is also as simple as the example, I would do it that way:
    =LET(array1,A2#,array2,E2#,INDEX(array1:array2,{1;2;3},{1,2,3,5,6,7}))
dscheikey
Bronze Contributor
Nov 14, 2022
mlylyly
For this task you can also use the CHOOSE() function in older Excel versions.
=CHOOSE({1,2},E4:E6,G4:G6)
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.

mlylyly.xlsx9 KB

Forum Discussion

Join two spill ranges to one range with two columns

12 Replies

Resources