vlookup column based on user input

[berniean]

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?

 

[OssieMac]

Hello Berniean,

Lets say you have a table of your items and the prices that looks like this.

Item ABC DEF XYZ
123 $1.08 $1.19 $1.27
345 $1.09 $1.15 $1.22
236 $1.08 $1.13 $1.24
432 $1.08 $1.14 $1.24
456 $1.07 $1.11 $1.23

Somewhere out of the way on your worksheet insert the numbers 2, 3, 4 in
separate cells. (These numbers represent the column numbers used in VLOOKUP
for ABC, DEF and ZYZ.)

Select the cell with 2.
Select Define names (See help for how to define names).
Define the name ABC. (Or your source name)

Select the cell with 3.
Select Define names
Define the name DEF.

Select the cell with 4.
Select Define names
Define the name XYZ.

Now in your table that looks like this.
Item Source Cost
123 ABC
345 DEF
456 DEF
236 XYZ
432 ABC

Assume cost is column C.
In cell C2 insert the following formula.
=VLOOKUP(A2,$F$1:$I$12,INDIRECT(B2),FALSE)

Copy the formula down.

 

[Max]

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

 

[OssieMac]

Hello Berniean,

I have posted this answer twice before this one and it seems to be
disappearing from the newsgroup so if it eventually appears multiple times
then my apologies.

Lets say you have a table of your items and the prices that looks like this.

Item ABC DEF XYZ
123 $1.08 $1.19 $1.27
345 $1.09 $1.15 $1.22
236 $1.08 $1.13 $1.24
432 $1.08 $1.14 $1.24
456 $1.07 $1.11 $1.23


Somewhere out of the way on your worksheet insert the numbers 2, 3, 4 in
separate cells. (These numbers represent the column numbers used in VLOOKUP
for ABC, DEF and ZYZ.)


Select the cell with 2.
Select Define names (See help for how to define names).
Define the name ABC. (Or your source name)


Select the cell with 3.
Select Define names
Define the name DEF.


Select the cell with 4.
Select Define names
Define the name XYZ.


Now in your table that looks like this.
Item Source Cost
123 ABC
345 DEF
456 DEF
236 XYZ
432 ABC


Assume cost is column C.
In cell C2 insert the following formula.
=VLOOKUP(A2,$F$1:$I$12,INDIRECT(B2),FALSE)


Copy the formula down.

 

[berniean]

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

 

[Max]

Good to hear. You're welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik