excel reference the first column of table

Question

Dog     Cat     Bird    Gary  
A       0       C       100%
B       0       B       
C       ok      D       100%
D       ok              100%
E       no      A       
F       ok      F       100%

Is it possible to reference the 1st 2nd or 3rd column by column number/position instead of column header? So instead of

Table1[@Dog]

It would look more like this.

Table1[@(1st column)]

In VBA it is.

ListObjects("Table1").ListColumns(1)

But I need to know if there is a formula way to do this? Also Just to make clear I DO NOT want to know what column number or letter it is in the worksheet. I want to reference the 1st column by position within the table, NOT header name. TY

Answer 1

If you want to refer to cells in the same row as your formula, which is what it looks like from your question, you can use INDEX.

Above, the formula =INDEX(Table2[@],2) refers to the second column in the same row as your formula.

If you don't want to refer to the row the formula is in, just refer to the whole table and INDEX on the row and column. For example, this refers to the fourth row in the second column:

=INDEX(Table2,4,2)

Note that Table2 refers to the data area of the table - not the headers - just like a database table.