+ Reply to Thread
Results 1 to 8 of 8

Vlookup - dynamic range reference?

  1. #1
    Registered User
    Join Date
    11-28-2005
    Posts
    7

    Vlookup - dynamic range reference?

    I am trying to do a vlookup that will look in a different range based on an input cell.

    =vlookup(d1,b1,2,false) where b1 changes to different sheets (i.e. if b1 = adam, then look in named range adam).

    would also like to do it without using named ranges but seem to be stuck. I tried using indirect(b1) but that didn't work.

    thanks in advance

    Adam

  2. #2
    Registered User
    Join Date
    11-28-2005
    Posts
    7

    no nested if

    don't want to use nested ifs as there are about 12 possible ranges

  3. #3
    Dave Peterson
    Guest

    Re: Vlookup - dynamic range reference?

    Why didn't this work?
    =vlookup(d1,indirect(b1),2,false)

    Are you sure you had a named range for the value in B1?

    Did it consist of at least 2 columns?



    aseanor wrote:
    >
    > I am trying to do a vlookup that will look in a different range based on
    > an input cell.
    >
    > =vlookup(d1,b1,2,false) where b1 changes to different sheets (i.e. if
    > b1 = adam, then look in named range adam).
    >
    > would also like to do it without using named ranges but seem to be
    > stuck. I tried using indirect(b1) but that didn't work.
    >
    > thanks in advance
    >
    > Adam
    >
    > --
    > aseanor
    > ------------------------------------------------------------------------
    > aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
    > View this thread: http://www.excelforum.com/showthread...hreadid=572769


    --

    Dave Peterson

  4. #4
    Registered User
    Join Date
    11-28-2005
    Posts
    7

    yeah I'm sure - additional info inside

    I made the named range myself.

    I was able to get the formula to work if I used indirect to concatenate the actual range address from the sheet name and the cell references but if I used indirect(c2) where c2 = range name, I get an answer but the wrong one.


    named range: bmo
    28
    sheet1 =B4&"!" c4:p24 <= using cells to assemble range reference
    =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
    =VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use name}

    =VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}

    thanks for your help.

    Adam

  5. #5
    Dave Peterson
    Guest

    Re: Vlookup - dynamic range reference?

    I'm sorry.

    I don't understand the explanation.

    aseanor wrote:
    >
    > I made the named range myself.
    >
    > I was able to get the formula to work if I used indirect to concatenate
    > the actual range address from the sheet name and the cell references but
    > if I used indirect(c2) where c2 = range name, I get an answer but the
    > wrong one.
    >
    >
    > named range: bmo
    > 28
    > sheet1 =B4&"!" c4:p24 <= using cells to assemble range reference
    > =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
    > =VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
    > name}
    >
    > =VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}
    >
    > thanks for your help.
    >
    > Adam
    >
    > --
    > aseanor
    > ------------------------------------------------------------------------
    > aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
    > View this thread: http://www.excelforum.com/showthread...hreadid=572769


    --

    Dave Peterson

  6. #6
    Registered User
    Join Date
    11-28-2005
    Posts
    7
    I was afraid of that. I have attached a zip of the sample file. Maybe that will help.

    Thanks again

    Adam

    Quote Originally Posted by Dave Peterson
    I'm sorry.

    I don't understand the explanation.

    aseanor wrote:
    >
    > I made the named range myself.
    >
    > I was able to get the formula to work if I used indirect to concatenate
    > the actual range address from the sheet name and the cell references but
    > if I used indirect(c2) where c2 = range name, I get an answer but the
    > wrong one.
    >
    >
    > named range: bmo
    > 28
    > sheet1 =B4&"!" c4:p24 <= using cells to assemble range reference
    > =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
    > =VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
    > name}
    >
    > =VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}
    >
    > thanks for your help.
    >
    > Adam
    >
    > --
    > aseanor
    > ------------------------------------------------------------------------
    > aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
    > View this thread: http://www.excelforum.com/showthread...hreadid=572769


    --

    Dave Peterson
    Attached Files Attached Files

  7. #7
    Debra Dalgleish
    Guest

    Re: Vlookup - dynamic range reference?

    Maybe this version:

    =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)

    give the incorrect answer because you didn't include FALSE as the last
    argument.

    aseanor wrote:
    > I made the named range myself.
    >
    > I was able to get the formula to work if I used indirect to concatenate
    > the actual range address from the sheet name and the cell references but
    > if I used indirect(c2) where c2 = range name, I get an answer but the
    > wrong one.
    >
    >
    > named range: bmo
    > 28
    > sheet1 =B4&"!" c4:p24 <= using cells to assemble range reference
    > =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
    > =VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
    > name}
    >
    > =VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked fine}
    >
    > thanks for your help.
    >
    > Adam
    >
    >



    --
    Debra Dalgleish
    Contextures
    http://www.contextures.com/tiptech.html


  8. #8
    Dave Peterson
    Guest

    Re: Vlookup - dynamic range reference?

    There are lots of people who won't open attachments. (I don't.) There are lots
    of people who don't connect to the newsgroups through excelforum so they can't
    open the attachment even if they wanted to.

    You're going to limit the number of responses by including attachments.

    If you try explaining your problem in plain text, you may get some answers.

    aseanor wrote:
    >
    > I was afraid of that. I have attached a zip of the sample file. Maybe
    > that will help.
    >
    > Thanks again
    >
    > Adam
    >
    > Dave Peterson Wrote:
    > > I'm sorry.
    > >
    > > I don't understand the explanation.
    > >
    > > aseanor wrote:
    > > >
    > > > I made the named range myself.
    > > >
    > > > I was able to get the formula to work if I used indirect to

    > > concatenate
    > > > the actual range address from the sheet name and the cell references

    > > but
    > > > if I used indirect(c2) where c2 = range name, I get an answer but

    > > the
    > > > wrong one.
    > > >
    > > >
    > > > named range: bmo
    > > > 28
    > > > sheet1 =B4&"!" c4:p24 <= using cells to assemble range reference
    > > > =VLOOKUP(D3,INDIRECT(C2),2) {doesn't work, gives wrong answer)
    > > > =VLOOKUP(D3,INDIRECT($C4&D4),2,FALSE) {works, but would rather use
    > > > name}
    > > >
    > > > =VLOOKUP(D3,bmo,2,FALSE) {this was my check, it worked

    > > fine}
    > > >
    > > > thanks for your help.
    > > >
    > > > Adam
    > > >
    > > > --
    > > > aseanor
    > > >

    > > ------------------------------------------------------------------------
    > > > aseanor's Profile:

    > > http://www.excelforum.com/member.php...o&userid=29161
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=572769
    > >
    > > --
    > >
    > > Dave Peterson

    >
    > +-------------------------------------------------------------------+
    > |Filename: vlookup.zip |
    > |Download: http://www.excelforum.com/attachment.php?postid=5225 |
    > +-------------------------------------------------------------------+
    >
    > --
    > aseanor
    > ------------------------------------------------------------------------
    > aseanor's Profile: http://www.excelforum.com/member.php...o&userid=29161
    > View this thread: http://www.excelforum.com/showthread...hreadid=572769


    --

    Dave Peterson

+ 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