Vlookup + join or collect

Stephanie Lapera

Hello,

I have a cell where I am collecting all unique districts participating in multiple events over the summer (All Districts column - multi select column).

I would like to separate those districts into their different segment levels (Columns S1 through S4). I am trying to use the table as a reference which lays out what levels each district is in (Segment Level and Districts columns).

I am having trouble on whether to use vlookup or match/index and how to combine it with join/collect/has/contains.

There will be 200+ districts separated into 4 segments in the table.

Once in the S1-S4 columns i can then count the total in each segment which I think I will easily be able to do.

The all districts column is pulling from another sheet but I am good on that formula. I can put this into 2 sheets if needed. Showing in one place for ease of explanation. Any help is appreciated. Thank you.

Genevieve P.

Hi @Stephanie Lapera

You can do this with JOIN(COLLECT! Either as a cross-sheet formula or as an in-sheet formula. Using your example of in-sheet, you can do something like this for P1:

=JOIN(COLLECT(Districts:Districts, [Segment Level]:[Segment Level], 1), CHAR(10))

The CHAR(10) at the end is what will separate your values into multi-select options. Then for S2 you just need to swap out the 1 for 2:

=JOIN(COLLECT(Districts:Districts, [Segment Level]:[Segment Level], 2), CHAR(10))

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Stephanie Lapera

Hi @Genevieve P.

Thank you for the help. The formula worked but I only need to separate the districts in the ALL Districts cell which is a collection of participating districts. The Segment Level and Districts column are just the reference. I have a total of 200+ districts as an option but I may only be working with 10+ at a time. Those are the ones I want to separate into the different segments. This will help me determine if there is equity across segments. Is there a way to modify the formula?

In the example I show districts A-H in the reference (Segment Level and Districts) but only worked with A-F (All Districts) which I want to be separated into the 4 different segments (S1 through S4).

Thanks!

Genevieve P.

Hi @Stephanie Lapera

My apologies for the delay! Thank you for clarifying.

The formula would need to be able to evaluate each individual District next to it's corresponding Segment Level, like you have in the example chart. There currently isn't a way to parse out the data from multiple selection in one cell and match this against the chart, the formula would evaluate the multi-select cell as one whole selection, if that makes sense.

We could potentially add together multiple IF statements to check for each possibility based on the column that you're putting the formula in to:

=IF(HAS([All Districts]@row, "A"), "A" + CHAR(10)) + IF(HAS([All Districts]@row, "E"), "E" + CHAR(10))

But with over 200 districts this doesn't sound like the best solution.