Using INDEX function with Multi Select Columns

Torsten Rich-Wimmer

Greetings,

I have been poking at this for a few weeks now, and I can't imagine that this is as complicated as I am making it.

Below is an overly basic version of what I need to build. The left (tan) columns represent a sheet that managers update to request eLearning for their units (Care-Areas) and the right (blue) columns represent a second sheet that matches an employee to the eLearning required based on the Care Area in which they work. For example, an employee working in Care Area 3 receives Courses 2 and 3.

The INDEX formula I am using works perfectly so long as the Care Area contains only one value:

=INDEX([eLearning Assignment]:[eLearning Assignment], MATCH([Assignment Index]@row, [Care-Area]:[Care-Area], 0))

This makes sense since it is using MATCH to locate the appropriate row; thus, we are scanning to match the entire contents of the associated Assignment Index row. I am wondering if there is another function besides MATCH that will return the relative position of a cell that contains one specific value in a multi-select cell populated with several others? We tried to use the HAS function, but I cannot figure out how to scan the entire column vs [Assignment Index]@row.

Bassam Khalil

Hi @Torsten Rich-Wimmer

Hope you are fine, please use the following formula and convert it to a column format formula:

=JOIN(COLLECT({Elearning Assignment}, {Care-Areas}, CONTAINS([Assignment Index]@row, @cell)))

{Elearning Assignment} reference the [Elearning Assignment] in sheet 1

{Care-Areas} reference the [Care-Areas] in sheet 1

the following screenshot shows the result

Sheet 1

Sheet 2

Bassam Khalil

Hi @Torsten Rich-Wimmer

Hope you are fine, please use the following formula and convert it to a column format formula:

=JOIN(COLLECT({Elearning Assignment}, {Care-Areas}, CONTAINS([Assignment Index]@row, @cell)))

{Elearning Assignment} reference the [Elearning Assignment] in sheet 1

{Care-Areas} reference the [Care-Areas] in sheet 1

the following screenshot shows the result

Sheet 1

Sheet 2

Torsten Rich-Wimmer

Thank you, Bassam!

I ran into another issue when plugging in the actual names of our Care-Areas. In the below example, the contents of 14A is also being pulled into 4A. Is there a way to search for 4A as an absolute?

My code is:

=JOIN(COLLECT([eLearning Assignment]:[eLearning Assignment], [Care-Area]:[Care-Area], CONTAINS([Assignment Index]@row, @cell), [F/SF/R]:[F/SF/R], CONTAINS([F/SF/R Index]@row, @cell)))

Bassam Khalil

@Torsten Rich-Wimmer

You are welcome and I will be happy to help you any time.