Search code examples
excelexcel-2007excel-formula

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

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.

Solution

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