How do you reference a table column relative to the table rather than sheet?

Copper Contributor



I'm just wondering if there's a cleaver way to reference a table column when using CHOOSECOLS(Table1,COLUMN(Table1[data])) when the table isn't in column A of the sheet without having to do COLUMN(...)-1 or refer to cell above the table that has a COLUMN() in it?


In other words, is there a native formula that returns the column/row position of a table column/row relative to the table itself?

3 Replies

Not to be pedantic, but doesn't this:
return the same as:


Perhaps this: =CHOOSECOLS(Table1,MATCH("Data",Table1[#Headers],0))

...I was thinking more along the lines of the CHOOSECOLS being part of a bigger formula and where it chooses multiple columns.
Looking for a more compact and dynamic way of choosing, say, the third column of a table if the table isn't starting in column A.
The shortest formula I can come up with to get the top-left column of a table is:


But to "choose" a set of columns from a table, perhaps something like this is efficient:

=HSTACK(Table1[Column a],Table1[Column c],Table1[Column e],Table1[Column g])