Make array with reference (no macro)

[gifariz]

As we know, the new excel release is super awesome with its array range and lambda function (along with other functions to work on array/lambda).
One problem that still makes me uncomfortable is making an array with reference.

We can make normal array as ={1,2,3;4,5,6}. But apparently we cannot make array with reference ={A1,C1,E1;A2:C2}.
Now I am still using this inelegant way to make array with reference =MAKEARRAY(2,3,LAMBDA(r,c,IF(r=1,IF(c=1,A1,IF(c=2,C1,IF(c=3,E1),INDEX(A2:C2,1,c))))

Is there simpler way to make array with reference?
(No macro)

 

[Rick Rothstein]

Try this (where the 1, 3 and 5 are the column numbers for Columns A, C and E)...

=INDEX(1:2,{1;2},{1,3,5})

 

[Rick Rothstein]

Actually, restricting the range for the INDEX function's first argument is probably better...

=INDEX(A1:E5,{1;2},{1,3,5})

 

[gifariz]

Try this (where the 1, 3 and 5 are the column numbers for Columns A, C and E)...

=INDEX(1:2,{1;2},{1,3,5})

Thank you. That works on above problem, but my example seems not quite general.
What if the reference is very far, and is dynamic array (size are changing)?

Here is another sample problem ={A1;AZ5#;TRANSPOSE(C50#)} where AE5# & C50# are dynamic arrays and different size, spanning vertical and horizontal respectively.
I cannot think of a way to do this with INDEX.

Actually, restricting the range for the INDEX function's first argument is probably better...

=INDEX(A1:E5,{1;2},{1,3,5})

Yes true, speed matters also.

 

[RICH937]

this will get you there, but it's not ideal if your dataset is much larger than this and you're pulling data from multiple rows/columns.

=VSTACK(CHOOSECOLS(A1:E1,1,3,5),CHOOSECOLS(A2:C2,1,2,3))

edit: never mind. I just read your second comment, and this will not address this at all.

 

[Anonymous1378]

=IFERROR(VSTACK(A1,AZ5#,TRANSPOSE(C50#)),"")? You'd basically be nesting VSTACK and HSTACK for ; and , respectively, as compared to stacking arrays in google sheets, if that's the comparison you're making.

 

[gifariz]

=IFERROR(VSTACK(A1,AZ5#,TRANSPOSE(C50#)),"")? You'd basically be nesting VSTACK and HSTACK for ; and , respectively, as compared to stacking arrays in google sheets, if that's the comparison you're making.

Right, I have been doing this since the release of VSTACK and HSTACK functions. My question was posted before those functions exist. Thanks tho

 

[Anonymous1378]

Right, I have been doing this since the release of VSTACK and HSTACK functions. My question was posted before those functions exist. Thanks tho

Oops, didn't notice that I accidentally replied to a year old thread. I blame Rich937 for the bump