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.