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.

Thanks

Answer 1

You could use: =COLUMN(MyTable[*]) - COLUMN(MyTable[A]) + 1, where * is the column whose index you want.

Answer 2

Do you mean:

Dim r As Range
MyLetter ="AA"
Set r = Range(MyLetter & "1")
MyIndex= r.Column

Edit re comment

Function GetRelativeColumn(Letter, RangeName)
Dim r As Range
Dim ColStart, ColRequired, ColTemp
Set r = Range(RangeName)

ColStart = r.Column
ColRequired = Range(Letter & "1").Column
ColTemp = ColRequired - ColStart + 1
If ColTemp < 1 Or ColTemp > r.Columns.Count Then
    MsgBox "Ooutside range"
Else
    GetRelativeColumn = ColTemp
End If
End Function

Answer 3

=slight modification to eJames' respones: =COLUMN(MyTable[ * ]) - MIN(COLUMN(MyTable)) + 1, where * is the column you want the index of.