Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Using INDEX function with Multi Select Columns

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.

Tags:

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • ✭✭✭✭✭✭
    Answer ✓

    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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)))

  • ✭✭✭✭✭✭

    @Torsten Rich-Wimmer

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions