6

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.

David
  • 4,665
  • 4
  • 34
  • 60
Dane O'Connor
  • 75,180
  • 37
  • 119
  • 173

6 Answers6

7

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.

Undo
  • 25,519
  • 37
  • 106
  • 129
Robert Mearns
  • 11,796
  • 3
  • 38
  • 42
  • I had to remove the image from your post because ImageShack has deleted it and replaced it with advertising. See http://meta.stackexchange.com/q/263771/215468 for more information. If possible, it would be great for you to re-upload them. Thanks! – Undo Sep 22 '15 at 00:49
5

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.

Brian Camire
  • 4,685
  • 2
  • 26
  • 23
  • 1
    Even though I don't think the OP wanted this solution specifically, this is exactly what I was looking for. Thank you. I had that forehead slapping moment as soon as I saw "=MATCH(" – Bobby Nov 20 '14 at 22:00
  • 2
    Using the string literal `"C"` in this formula means that if you change the name of the C column in your referenced table this formula will break. Robert Mearns' answer is able to use `MyTable[C]` as the reference which will update as you rename the C column in the referenced table. – brittohalloran Sep 06 '18 at 20:18
1

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

1

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • I think this is making 2 many assumptions. Basically I'd like a way to define a table "MyTable" which is any range. I then want to be able to get the index of a named column relative to the first column in "MyTable" can this function do that? Remember I'm using structured references. Either way ty – Dane O'Connor Dec 12 '08 at 22:38
0

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

e.James
  • 116,942
  • 41
  • 177
  • 214
  • Is there a way to get the first column in a table? I don't rather not be locked into having "A" always be at the first index. Thanks – Dane O'Connor Dec 12 '08 at 22:35
0

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...

Community
  • 1
  • 1
studgeek
  • 14,272
  • 6
  • 84
  • 96