When you're referencing an array using "#", is there a more straightforward way to reference a specific column or row in that array besides INDEX?

[alexcr]

For example, let's say there's an array that begins on a worksheet at cell B5, and you want to reference the second column of that array.

I know one way to do that is "INDEX(B5#,,2)", but is there perhaps an even more straightforward way to reference this column without resorting to a function?

 

[Eric W]

I'm not sure how much straightforward you can get, but here are 2 more options:

=FILTER(B5#,{0,1,0})

assuming 3 columns.

=DROP(TAKE(B5#,,2),,1)

 

[Eric W]

Another new function, that might be most intuitive:

=CHOOSECOLS(B5#,2)

 

[alexcr]

Another new function, that might be most intuitive:

=CHOOSECOLS(B5#,2)

Thanks, @Eric W! I didn't even think of the CHOOSECOLS option for some reason. But, yeah, that makes it slightly more intuitive, which is good.

 

[awoohaw]

comparison formulas:

   =IF(B5# = C3, TRUE, FALSE)