0

i need to refrence A1:A5 (which will return {1;2;3;4;5}) and then add B2:B5 to the end of that (which will return {6;7;8;9;10}), so the finished product will look like this {1;2;3;4;5;6;7;8;9;10}.

does anyone have any ideas? thanks

Dave
  • 249
  • 1
  • 11

1 Answers1

0

This will return the array desired:

INDEX(A1:B5,N(IF(1,MOD(ROW(1:10)-1,5)+1)),N(IF(1,INT((ROW(1:10)-1)/5)+1)))

You can test it by doing:

=INDEX(INDEX(A1:B5,N(IF(1,MOD(ROW(1:10)-1,5)+1)),N(IF(1,INT((ROW(1:10)-1)/5)+1))),7)

Which, when confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode, will return the value in B2.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • THAT IS AMAZING!! i will display what i did with that soon, but can you please explain: 1) why you use the (seemingly superfluous) N function? 2) why you use the (seemingly superfluous) If function, as you just typed TRUE in the logical statemnt...?? 3) i always thought that index cant take array formulas/constants in the row/column index argument. can you explain how yours worked? [ 4) why doesn't microsoft make a larger evaluate formula window :)???] Thanks so much!! you are amazing! – Dave Mar 14 '18 at 02:15
  • The `N` and `IF` combination return arrays to the INDEX which it will accept. It is a loop hole that works. Not sure why that magical combination works but it does. – Scott Craner Mar 14 '18 at 04:25
  • I am absolutely blown away by this (and the one who figured that out..)! Had i known this earlier i would have saved tons of time!!!. i recently did this formula to add the second and fourth column of an index array: =sum(sumproduct(index(A1:A5,)*{0,1,0,1,0}).... Thanks for your help! – Dave Mar 14 '18 at 05:20
  • Now, to take it a step further, can we delete the duplicates in the same formula?? i tried playing around with it for a while with no success (yet). (to make it even crazier, how about deleting duplicates and sorting..... but at least deleting duplicates..) – Dave Mar 14 '18 at 05:28
  • @Dave https://stackoverflow.com/questions/47294089/array-formula-to-return-an-array-without-duplicates-without-vba – Scott Craner Mar 14 '18 at 12:50