Forum Discussion

mlylyly's avatar
mlylyly
Copper Contributor
Nov 14, 2022
Solved

Join two spill ranges to one range with two columns

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...
excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    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's avatar
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's avatar
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}))

 

Resources