One VLOOKUP cell gives me a column list of returns

SRenner
SRenner
edited 12/09/19 in Formulas and Functions

I am looking to have a drop down cell called "run name" that when changed will populate all the stores on that run. 

I was using VLOOKUP from a master store/run sheet to do this but could only figure out how to get one store to populate rather than the entire list of stores. 

Any help with this would be appreciated. 

 

Thanks

Scott

 

 

 

smartsheet question for community.JPG

«13

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you have a "master sheet" where every store is listed along with it's run?

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Scott,

    Can you share the formula you're using?

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • SRenner
    SRenner
    edited 09/05/19

    Below is what I'm working with. The first picture is the Master data sheet.  It lists the run name in column 1 and the store name in column 2.  I would like to create a drop down on a separate sheet to choose a run name and the stores populate.  From there, I will create other formulas that work off of the store listing to give me other indices.  

    The second picture is the formula I am using on my drop down sheet which is in the first post to this thread.   It works for the first cell but I'm not sure if there is a way to make the rest of the cells populate accordingly. 

    thanks

    Scott 

    master data sheet.JPG

    formula vlookup.JPG

  • Yes please see my reply below in this thread.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    I'd be happy to take a look!

    Can you maybe share the sheet(s) or a copy of them? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I have created a workspace for you.  thank you 

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Thanks!

    I'll take a look and get back to you!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Could you let us know your solution here?

     

    I personally am thinking hiding helper columns where you essentially use a JOIN/COLLECT to pull all stores together into a long string then parsing them out down the column, but I am not sure if this is the most efficient or not.

    thinkspi.com

  • SRenner
    SRenner
    edited 09/11/19

    I was able to find a work around that had me transpose my raw data store list. 

    I used VLOOKUP to see 20 columns out and worked it down my columns

    Example:

    Run Name(Column 1)  Store names(Columns 2-21)

    =VLOOKUP($[Run Name]$1, {Store master Range 1}, 2, false  through =VLOOKUP($[Run Name]$1, {Store master Range 1}, 20, false)

    formula vlookup.JPG

    master data sheet.JPG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Scott transposed the sheet instead, and I gave him another option where we could use INDEX/MATCH or VLOOKUP with a unique identifier for the rows and then count up to show them after each other.

    I'm waiting for a response if that would work or if it's ok with the transposed sheet.

    I'll get back to the post when I have an answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andree, 

    I stayed with the VLOOKUP from the transposed master at the moment.  I feel when I have an updated store master I will have to repeat that transposed process.  I think I will have to come back to this in the future.  I have moved on to the next part of my project though to see if what I'm wanting to happen will actually work for me. 

    thank you all for the help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    The most important part is that it works!

    I'm always happy to help!

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah! Ok. Definitely not the route I was thinking. Haha. I like it. yes

    thinkspi.com

  • This is what I used.  The transposing of the master data was time consuming.  

    I would like to be able to use the other formulas Andree was talking about to save time in the future. If someone would like to throw those formulas out here on this thread I will surly use it when my master data needs updating.

     

    thanks all.

    Scott

    master data sheet.JPG

    formula vlookup.JPG

  • Ok guys, @Paul Newcome and @Andrée Starå

    Im back on this thread. the Vlookup was a failure as you probably figured.

    I am seeing articles about INDEX and possibly MATCH formulas to bring across.

    the idea i have is for the Run name column to be a drop down for the one cell only. Then it populates all the stores on that run in the order that they appear. like the one above.

    I am currently using =INDEX({DRIVER STORE MASTER 02242020 Range 1}, 1, 2) to bring over the cell based on the row and column but I need some type of Identifier to only pull the run name that I have selected in the drop down cell.

    is there a way to combine the formula above to an vlookup of sorts?

    thanks!

    Scott