How to get table column index using structured link in excel?

Question

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

So, for example, for a MyTable table with columns A , B , C I would like to write:

 =GetIndex(MyTable[C]) 

and return it 3.

Now I just check that the range of the table starts in the first column of the sheet, and I write

 =Column(MyTable[C]) 

but I want something to be more reliable.

Answer 1

A suitable formula based on your example would be

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

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

The second part of the COLUMN (MyTable) formula 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 provided.

However, if you used INDEX instead of VLOOKUP, you would not need to do this. For example, if you want to find the value of C in a string (presumably no more than one), where A is 2, you can use a formula like =INDEX(MyTable[C],MATCH(2,MyTable[A],0)) , which well self-documenting.

Answer 3

Do you mean:

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

Edit 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 4

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

Answer 5

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

Answer 6

What is your ultimate goal? You might be better off using SUMIF (as I describe here ) or INDEX (as I describe here ) to access your values ​​rather than returning to a row / column ...