How do get the index of a table's column by using a structured reference in excel?

Question

I have a table with 3 columns. I want to write a formula that, given a structured reference, returns the index of the column. This will help me write VLookup formulas using the structured reference.

So, for example, for the table MyTable with columns A, B, C I'd like to be able to write:

=GetIndex(MyTable[C])

and have it return 3.

Right now I just make sure the table range starts on the sheet's first column and I write

=Column(MyTable[C])

but I want something a more robust.

Answer 1

A suitable formula based on your example would be

=COLUMN(MyTable[C])-COLUMN(MyTable)+1

The first part of the forumla COLUMN(MyTable[C]) will return the column number of the referenced column.

The second part of the formula COLUMN(MyTable) will always return the column number of the first column of the table.

Answer 2

Another solution to the question you asked (or something close to it) is to use something like =MATCH("C",MyTable[#Headers],0), which will return 3 in the example you posted.

However, if you used INDEX instead of VLOOKUP, you wouldn't need to do this. For example, if you wanted to find the value of C in the row (assumingly there is no more than one) where A is equal to 2, you could use a formula like =INDEX(MyTable[C],MATCH(2,MyTable[A],0)), which is nicely self-documenting.