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.
