lookup column using text not col number

[Joe]

The normal vlookup uses a column index number to select the column for your
result. I am trying to do a table lookup with a value that would find a
different column based on input. This lookup would be in many places in a
spreadsheet and at somepoint someone will probably change the column
arrangement.

There are different pricing policies in effect ie 1, 2, 3, etc. So at times
i want to find the column for price1, price2 or price3. So price1 is in col
6 but sometime might get moved to col 8. And I would prefer the lookup
formula move with it.

If you do calculations inside a table there are commands with [] that use
the column name. Can that be done outside a table?

 

[Glenn]

The normal vlookup uses a column index number to select the column for your
result. I am trying to do a table lookup with a value that would find a
different column based on input. This lookup would be in many places in a
spreadsheet and at somepoint someone will probably change the column
arrangement.

There are different pricing policies in effect ie 1, 2, 3, etc. So at times
i want to find the column for price1, price2 or price3. So price1 is in col
6 but sometime might get moved to col 8. And I would prefer the lookup
formula move with it.

If you do calculations inside a table there are commands with [] that use
the column name. Can that be done outside a table?

http://www.contextures.com/xlFunctions03.html#IndexMatch3