Q: Named range based on cell value? in Excel General Questions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Mark 11/13/2007 2:14 PM PST
  Question
  I want to do a vlookup based on a range that is based on a cell.

So, if cell A1 = "CA" use the CA_REG named range in my formula.
If cell A1 = "NV" use the NV_REG named range in my formula.
etc. etc.

There will be about a dozen values that the cell can have, so I can't use a
nested if.

Any ideas?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Chip Pearson 11/13/2007 2:45 PM PST
  Answer
  Try something like the following:

=VLOOKUP(xyz,INDIRECT(INDEX({"CA_REG","NV_REG","AB_REG"},0,MATCH(A1,{"CA","NV","AB"},0))),2,FALSE)

Change the 'xyz' to the value you want to look up with VLOOKUP.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Mark" <Mark@discussions.microsoft.com> wrote in message
news:5A188125-1B2A-45AD-944D-DAAF4486DA50@microsoft.com...
>I want to do a vlookup based on a range that is based on a cell.
>
> So, if cell A1 = "CA" use the CA_REG named range in my formula.
> If cell A1 = "NV" use the NV_REG named range in my formula.
> etc. etc.
>
> There will be about a dozen values that the cell can have, so I can't use
> a
> nested if.
>
> Any ideas?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Mark 11/13/2007 2:56 PM PST
   
  Thanks!!! Works great!

"Chip Pearson" wrote:

> Try something like the following:
>
> =VLOOKUP(xyz,INDIRECT(INDEX({"CA_REG","NV_REG","AB_REG"},0,MATCH(A1,{"CA","NV","AB"},0))),2,FALSE)
>
> Change the 'xyz' to the value you want to look up with VLOOKUP.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:5A188125-1B2A-45AD-944D-DAAF4486DA50@microsoft.com...
> >I want to do a vlookup based on a range that is based on a cell.
> >
> > So, if cell A1 = "CA" use the CA_REG named range in my formula.
> > If cell A1 = "NV" use the NV_REG named range in my formula.
> > etc. etc.
> >
> > There will be about a dozen values that the cell can have, so I can't use
> > a
> > nested if.
> >
> > Any ideas?
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Mark 11/13/2007 3:05 PM PST
   
  By the by, using your formula as a template, I think I found a bit of a
slimmer solution:

=VLOOKUP("xyz",CHOOSE(MATCH(A2,{"CA","NV"},0),CA_REG,NV_REG),2,FALSE)

So far, it seems to work. And again, thank you so much for your solution.

-Mark



"Chip Pearson" wrote:

> Try something like the following:
>
> =VLOOKUP(xyz,INDIRECT(INDEX({"CA_REG","NV_REG","AB_REG"},0,MATCH(A1,{"CA","NV","AB"},0))),2,FALSE)
>
> Change the 'xyz' to the value you want to look up with VLOOKUP.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:5A188125-1B2A-45AD-944D-DAAF4486DA50@microsoft.com...
> >I want to do a vlookup based on a range that is based on a cell.
> >
> > So, if cell A1 = "CA" use the CA_REG named range in my formula.
> > If cell A1 = "NV" use the NV_REG named range in my formula.
> > etc. etc.
> >
> > There will be about a dozen values that the cell can have, so I can't use
> > a
> > nested if.
> >
> > Any ideas?
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies