One VLOOKUP cell gives me a column list of returns

SRenner

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

 

 

 

Paul Newcome

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

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

SRenner

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 

SRenner

Yes please see my reply below in this thread.

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, andree@getdone.se)

SRenner

I have created a workspace for you.  thank you 

Andrée Starå

Thanks!

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

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.

SRenner

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)

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!

SRenner

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å

Excellent!

The most important part is that it works!

I'm always happy to help!

 

Paul Newcome

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

SRenner

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

SRenner

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