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

=VLOOKUP(A1,INDIRECT(B1),2,FALSE)

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.