Results 1 to 5 of 5

Vlookup using named ranges

This is a discussion on Vlookup using named ranges within the Excel Questions forums, part of the Question Forums category; I am looking to use a Vlookup function with a named range. The problem is that users have the option ...

  1. #1
    New Member
    Join Date
    Jun 2003
    Posts
    2

    Default Vlookup using named ranges



    I am looking to use a Vlookup function with a named range. The problem is that users have the option of selecting a "letter" eg W from a validation list. Once a letter is selected it determines, via a lookup the name of the "Named Range", ie EG;

    If the user selects W, then the named range will be "ACT1". the value "ACT1" will be placed in a destination cell. The vlookup function will then lookup a value in the selected named range being ACT1. Therefore is the destination cell that the "ACT1" was placed in was [D1], then the formula would read as:

    VLOOKUP(lookup_value,[D1],4,FALSE)

    The result I get is #N/A. I don't know how to get the formula to realise that it needs to lookup the value in the range specified by the name in cell [D1]. Is there a solution to this?

    Please also not that the named range "ACT1" is on a seperate worksheet tab within the same workbook.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,424

    Default Re: Vlookup using named ranges

    Try:

    =VLOOKUP(lookup_value,INDIRECT(D1),4,FALSE)

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    149

    Default Re: Vlookup using named ranges

    =VLOOKUP(lookup_value,INDIRECT(D1),4,FALSE)

    With D1 being where your ACT1/ACT2 is displayed.


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,247

    Default Re: Vlookup using named ranges

    =VLOOKUP(lookup_value,IF(dropdown_cell="W",ACT1,ACT2),4,0)

    if you have just 2 choices.

  5. #5
    New Member
    Join Date
    Jun 2003
    Posts
    2

    Default Re: Vlookup using named ranges

    Thank You very much, the INDIRECT() function worked perfectly. Thanks for taking time out to assist me.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com