Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: vlookup range on multiple sheets?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all,

    I've got info in A1:G100 on sheet2 through
    sheet5.

    I want to write a vlookup formula on sheet1
    that looks for a value on any sheet. There
    will only be one instance of the value, but
    it could be on sheet2, or sheet4, or...

    Can this be done? If so, HOW?!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-03-27 13:21, Duane wrote:
    Hello all,

    I've got info in A1:G100 on sheet2 through
    sheet5.

    I want to write a vlookup formula on sheet1
    that looks for a value on any sheet. There
    will only be one instance of the value, but
    it could be on sheet2, or sheet4, or...

    Can this be done? If so, HOW?!
    Duane,

    Are these "tables" (a) true lookup tables or (b) data that you want to treat as such?

    If (a), I'd suggest to pull them together into a single table in a sheet of its own.

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default


    Example with 2 ranges; I named the ranges (rL1 and rL2) but it is not necessary to name the ranges.
    Revise references as necessary. For additional ranges copy from "&" and edit as necessary.

    =IF(AND(LEN(A8),COUNTIF(rL1,A8)),VLOOKUP(A8,rL1,2,0),"")&IF(COUNTIF(rL2,A8),VLOOKUP(A8,rL2,2,0),"")


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 13:45, Aladin Akyurek wrote:
    On 2002-03-27 13:21, Duane wrote:
    Hello all,

    I've got info in A1:G100 on sheet2 through
    sheet5.

    I want to write a vlookup formula on sheet1
    that looks for a value on any sheet. There
    will only be one instance of the value, but
    it could be on sheet2, or sheet4, or...

    Can this be done? If so, HOW?!
    Duane,

    Are these "tables" (a) true lookup tables or (b) data that you want to treat as such?

    If (a), I'd suggest to pull them together into a single table in a sheet of its own.

    Aladin
    Hey Aladin,

    That's the method I figured I'd have to use, but I was REALLy hoping there was "some niftier way" to do it. Let's hope the folks in Redmond have plans to increase vlookup functionality (using negative integers for the column variable would also be much simpler than using INDEX and MATCH too, wouldn't it?!)

    Using this approach in effect doublicates the data, which is no fun when having to send as an email attachment; but since it's going to sit on a shared drive, it's probably the simpler of the two methods...

    _________________
    Regards, Duane

    [ This Message was edited by: Duane on 2002-03-27 14:12 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 14:02, Dave Patton wrote:

    Example with 2 ranges; I named the ranges (rL1 and rL2) but it is not necessary to name the ranges.
    Revise references as necessary. For additional ranges copy from "&" and edit as necessary.

    =IF(AND(LEN(A8),COUNTIF(rL1,A8)),VLOOKUP(A8,rL1,2,0),"")&IF(COUNTIF(rL2,A8),VLOOKUP(A8,rL2,2,0),"")

    Hey Dave,

    Yep, that was a method to consider, but I was cringing at the thought of having to include a new string of functions for each sheet. It's fine when I've got a small few, but if this workbook was managing 35 ranges, I'd hate to have to keep track of where I was in the middle of writing out this formula!

    Oh well, thanks, I'll keep this method in mind...

    _________________
    Regards, Duane

    [ This Message was edited by: Duane on 2002-03-27 14:10 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not that you'd necessarily want to, but you could use

    =VLOOKUP(A1,INDIRECT("sheet"&COUNTIF(Sheet2!A1:A100,A1)*2+COUNTIF(Sheet3!A1:A100,A1)*3+COUNTIF(Sheet4!A1:A100,A1)*4+COUNTIF(Sheet5!A1:A100,A1)*5&"!$a$1:$g$100"),7,0)

    given that you only have one value and your sheets are names as you described.

  7. #7
    Board Regular
    Join Date
    Aug 2004
    Location
    Minneapolis,MN
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vlookup range on multiple sheets?

    for two sheets with referance on sheet1 and data on sheet2 and three

    =IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),VLOOKUP(A2,Sheet3!A:B,2,FALSE),VLOOKUP(A2,Sheet2!A:B,2,FALSE))

Some videos you may like

User Tag List

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
  •