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.

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：

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.

### Answer：

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：

You could use: `=COLUMN(MyTable[`

, where `*`

]) - COLUMN(MyTable[A]) + 1`*`

is the column whose index you want.

### Answer：

=slight modification to eJames’ respones:

=COLUMN(MyTable[ * ]) – MIN(COLUMN(MyTable)) + 1, where * is the column you want the index of.

### Answer：

What is your end goal? You may be better off using SUMIF (as I describe here) or INDEX (as I describe here) to access your values rather than jumping back to row/column…

Tags: excelexcel, struct