+ Reply to Thread
Results 1 to 7 of 7

Vlookup with variable worksheet reference

  1. #1
    Registered User
    Join Date
    05-23-2006
    Posts
    4

    Vlookup with variable worksheet reference

    Hello,

    I'm trying to run a vlookup where the worksheet (within the file) referenced would depend on the value of a certain cell.

    For example, if B5 could be a number different fruits, and there were different worksheets named after the fruit options, I could have one cell where I would look up something on worksheet 'Apple' if B5="Apple" or on worksheet 'Grape' if B5="Grape".

    When B5="Apple",
    =VLOOKUP(A2,'Apple'!A1:C37,3,FALSE) will give me the correct value, but both
    =VLOOKUP(A2,B5!A1:C37,3,FALSE)
    and
    =VLOOKUP(A2,'B5'!A1:C37,3,FALSE)
    will open a browse window and ask me to update the values for B5.

    Any help would be much appreciated.

  2. #2
    Registered User
    Join Date
    06-25-2005
    Posts
    46
    Hi,

    How many criteria are there in the list? If there only a few, you could put it in an IF statement.

    All the best,


    Roly

  3. #3
    Registered User
    Join Date
    05-23-2006
    Posts
    4
    Quote Originally Posted by roly

    How many criteria are there in the list? If there only a few, you could put it in an IF statement.
    Eighteen. I could do it that way or by naming the ranges that I want to select from, but if there's a cleaner way to do it, that's my preference.

    Thanks for your help!

  4. #4
    Larry S
    Guest

    Re: Vlookup with variable worksheet reference

    From your example below, I will assume column B will contain the COMPLETE
    name of the corresponding worksheet you want to pull the data from. then in
    your VLOOKUP formula below, replace 'Apple'!A1:C37 with

    INDIRECT(CONCATENATE("'",B5,"'!A1:C37"))

    The CONCATENATE works like the "&" in that it is building a text string, in
    the case of peaches 'PEACHES'!A1:C37 and the INDIRECT allows you to
    addressess the cell range.

    Hope this helps.


    "trempnvt" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello,
    >
    > I'm trying to run a vlookup where the worksheet (within the file)
    > referenced would depend on the value of a certain cell.
    >
    > For example, if B5 could be a number different fruits, and there were
    > different worksheets named after the fruit options, I could have one
    > cell where I would look up something on worksheet 'Apple' if B5="Apple"
    > or on worksheet 'Grape' if B5="Grape".
    >
    > When B5="Apple",
    > =VLOOKUP(A2,'Apple'!A1:C37,3,FALSE) will give me the correct value, but
    > both
    > =VLOOKUP(A2,B5!A1:C37,3,FALSE)
    > and
    > =VLOOKUP(A2,'B5'!A1:C37,3,FALSE)
    > will open a browse window and ask me to update the values for B5.
    >
    > Any help would be much appreciated.
    >
    >
    > --
    > trempnvt
    > ------------------------------------------------------------------------
    > trempnvt's Profile:
    > http://www.excelforum.com/member.php...o&userid=34710
    > View this thread: http://www.excelforum.com/showthread...hreadid=544779
    >




  5. #5
    Max
    Guest

    Re: Vlookup with variable worksheet reference

    Think we could use INDIRECT in the vlookup, viz.:
    =VLOOKUP(A2,INDIRECT("'"&B5&"'!A1:C37"),3,FALSE)
    where B5 houses the sheetname
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---


  6. #6
    Registered User
    Join Date
    05-23-2006
    Posts
    4
    Thank you all for your help! The INDIRECT trick worked.

  7. #7
    Max
    Guest

    Re: Vlookup with variable worksheet reference

    "trempnvt" wrote:
    > Thank you all for your help! The INDIRECT trick worked.


    Glad it worked for you !
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1