I have a spreadsheet that needs to be able to do a dynamic lookup with the
column to look in determined by the content of a cell. Basically, the cost of
an item is determined by where it came from. The same item may be sourced
several places. I have a list of items with columns for the various sources.
I have another tab in the same spreadsheet that will look up the cost based
on the source. But I won't know the source in advance.
A B C
1 Item# Source Cost
2 123 ABC vlookup(a2,"source list",ABC column,false)
3 345 DEF vlookup(a3,"source list",DEF column,false)

The Source in column B is the result of user input. The vlookup has to find
the column in the source tab based on that input. I've tried Index and Match
and some others but have had no success. Is this type of lookup even possible
in Excel?

You could try using MATCH to return the required col number,
eg: for this line:
2 123 ABC vlookup(a2,"source list",ABC column,false)
it could look like this in D2:
=VLOOKUP(A2,Sheet2!A:IV,MATCH(C2,Sheet2!$A$1:$IV$1 ,0),0)
where the lookup value input in C2, viz.: ABC
is to be found amongst the col headers in Sheet2!$A$1:$IV$1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"berniean" wrote:
I have a spreadsheet that needs to be able to do a dynamic lookup with the
column to look in determined by the content of a cell. Basically, the cost of
an item is determined by where it came from. The same item may be sourced
several places. I have a list of items with columns for the various sources.
I have another tab in the same spreadsheet that will look up the cost based
on the source. But I won't know the source in advance.
A B C
1 Item# Source Cost
2 123 ABC vlookup(a2,"source list",ABC column,false)
3 345 DEF vlookup(a3,"source list",DEF column,false)

The Source in column B is the result of user input. The vlookup has to find
the column in the source tab based on that input. I've tried Index and Match
and some others but have had no success. Is this type of lookup even possible
in Excel?

Max, this is perfect! Thank you very much...bernie

"Max" wrote:

You could try using MATCH to return the required col number,
eg: for this line:
2 123 ABC vlookup(a2,"source list",ABC column,false)
it could look like this in D2:
=VLOOKUP(A2,Sheet2!A:IV,MATCH(C2,Sheet2!$A$1:$IV$1 ,0),0)
where the lookup value input in C2, viz.: ABC
is to be found amongst the col headers in Sheet2!$A$1:$IV$1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"berniean" wrote:
I have a spreadsheet that needs to be able to do a dynamic lookup with the
column to look in determined by the content of a cell. Basically, the cost of
an item is determined by where it came from. The same item may be sourced
several places. I have a list of items with columns for the various sources.
I have another tab in the same spreadsheet that will look up the cost based
on the source. But I won't know the source in advance.
A B C
1 Item# Source Cost
2 123 ABC vlookup(a2,"source list",ABC column,false)
3 345 DEF vlookup(a3,"source list",DEF column,false)

The Source in column B is the result of user input. The vlookup has to find
the column in the source tab based on that input. I've tried Index and Match
and some others but have had no success. Is this type of lookup even possible
in Excel?

Good to hear. You're welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"berniean" wrote in message
...
Max, this is perfect! Thank you very much...bernie