Archive of Mr Excel Message Board
Back to Forms in Excel VBA archive index
Back to archive home
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.
|Check out our Excel Resources
=VLOOKUP(A1,INDIRECT("SHIRTS"),2,FALSE) [nt]Posted by Mark W. on February 15, 2002 7:56 AM
Re: Using VLOOKUP with named ranges.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
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.