Using VLOOKUP with named ranges.
Posted by Jeff Callow on February 15, 2002 7:43 AM
I'll give a more simplified version of my problem to make it clear. Let's say I have three tables with named ranges SHIRTS, PANTS, and SWEATERS. Each range simply has the product number of the item and its price.
I have a separate list of various model numbers and want to look up the prices. In cell A1, I will have the model number. In cell B1, I will write
write "SHIRTS", "PANTS", or "SWEATERS" in the cell to signify what table I want my model number to be looked up in. So if it says "SHIRTS" in B1, I want the vlookup in C1 to look like =VLOOKUP(A1, SHIRTS, 2, FALSE). However, Excel does not translate the text "SHIRTS" in B1 to the named range SHIRTS. Is there anyway to accomplish this without doing something like "=VLOOKUP(A1, if(B1="SHIRTS", SHIRTS, if(B1="PANTS", PANTS, SWEATERS)))" This gets quite large as more tables are added. Thanks for the help.
Posted by Mark W. on February 15, 2002 7:56 AM
=VLOOKUP(A1,INDIRECT("SHIRTS"),2,FALSE) [nt]
Posted by Dave Gibson on February 15, 2002 10:39 AM
You've probably figured this out already, but I think what Mark W. intended was
=VLOOKUP(A1,INDIRECT(B1),2,FALSE)
