VLOOKUP across multiple sheets using dependent dropdown
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VLOOKUP across multiple sheets using dependent dropdown
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP across multiple sheets using dependent dropdown

    Using a dependent dropdown, I have multiple values all with different source locations. I have used VLOOKUP to automatically populate a cell next to a drop-down.

    =VLOOKUP(C13,'SheetX'!C5:D5000,2,FALSE)

    However if I select B from the drop-down, how do I get VLOOKUP to do the same thing? The source info for B is on another sheet in another cell group. I've read it can be done with INDIRECT or INDEX/MATCH, but how to nest this formula is where I'm lost.

    Any advice?

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    453
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    When you select "B" from the drop down, does that mean you're preforming the lookup on SheetB ?

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    Let's say my dropdown (C13) on Sheet1 contains apple, orange, and banana. "Apple" data is on Sheet2 C4:D500. "Orange" data is on Sheet2 G4:H500. "Banana" data is on Sheet3 E5:F500.

    I'm attempting to populate D13 on Sheet1 with the adjacent data immediate to the cell right of my selection.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    453
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    I have the drop-down in B13, the lookup value in C13 and the answer (this formula) in D13:

    Code:
    =VLOOKUP(C13,INDIRECT("Sheet"&B13&"!C5:D5000"),2,FALSE)
    Just read your post and that wasn't my initial understanding. Let me think about it while I'm sure others are as well.
    Will the dropdown have more than 3 items with different ranges? If so, would you consider having a "directory" that points out what the ranges are and sheets?
    Last edited by kweaver; Jun 20th, 2019 at 04:44 PM.

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    453
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    Assuming I (a) understand what you mean in previous post and (b) you can have a directory of sorts,
    I put a "directory" in C15:D17

    Apple SheetA!C4:D500
    Orange SheetA!G4:H500
    Banana SheetB!E5:F500

    Then, D13 looks like this:

    Code:
    =VLOOKUP(C13,INDIRECT(VLOOKUP(C13,C15:D17,2,FALSE)),2,FALSE)
    Hope this helps as you adjust for your data and ranges.

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    Let me clarify:
    I have a spreadsheet on Sheet 1. In cell A1 there is a dependent drop-down with selections Apple, Orange, Banana.
    In Sheet2 the info for Apple is displayed from A1-B1. B2 value is the word "red"
    In Sheet2 the info for Orange is displayed from E1-F1, F1's Value is orange.
    In Sheet3 the info for Banana is displayed from C1-D1. D1 being yellow.

    I want to select Apple from the drop-down menu (sheet 1, A1) and automatically populate B1 with the word Red.
    Select Banana and have it populate B1 with Yellow.

    It's fairly simple when the source info is all in one place, but given my source info is in different sheets, and I'm using one dropdown for all the selections, I lose sight of how the formula should be ordered.

  7. #7
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    453
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    Well, you can see my confusion?! You initially said the drop down was in C13. And you are now saying there are different ranges from what you said initially.

    So, does Sheet2 in A1:B1 look like this?

    Apple Red

    And Sheet2 in E1:F1 look like this?

    Orange Orange

    And Sheet3 in C1:D1 look like this?

    Banana Yellow

    Then, Sheet1!B1 could be this?

    Code:
    =IF("Apple" = A1,Sheet2!B1, IF("Orange" = A1, Sheet2!F1,IF("Banana"=A1,Sheet3!D1,"No Fruit")))

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    So yes, the drop-down I'm using happens to be in C13, but the same concept would work if I change parameters to accommodate positioning. I gave you the example in A's and B's because I thought that would be easier to explain. I can tell I'm not making this easier.

    =VLOOKUP(C13,'SheetX'!C5:D5000,2,FALSE)

    If I use this formula, and I select "Apple" in the drop-down, it also automatically fills D13 with the coinciding value next to it.
    As soon as I select Banana, it doesn't know where to find the coinciding information, because the formula is based on only the one selection. I want the formula to understand that no matter what I select, it will also fill D13 with whatever is coinciding next to that particular selected value.

    Is that any better?!?!

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,145
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    You need something like this.
    Search in a range on a sheet, if it does not exist, search on another sheet, if there is no search on another sheet.

    =IFERROR(VLOOKUP(C13,Sheet2!C5:D5000,2,0),IFERROR(VLOOKUP(C13,Sheet2!E5:F5000,2,0),IFERROR(VLOOKUP(C13,Sheet2!G5:H5000,2,0),"Dont exists")))
    Regards Dante Amor

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP across multiple sheets using dependent dropdown

    Oh Yaaaaaassssssss, Dante!
    Thank you so much. This will work.

Some videos you may like

User Tag List

Tags for this Thread

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
  •