Matching using Multi-select column values

I have the following three columns Idx, Stage and Steps.

Steps is a Dropdown Multi-Select column.

I want to use the step value from column in another sheet to find which row that step belongs to so I can return the Idx or Stage values from a formula.





✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you explain in more detail? Are you able to provide a screenshot of the other sheet as well as a screenshot with manually entered data that reflects your desired outcome?

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    The other sheet for the sake of this example has two columns, one called step, which has the value to match against the steps column here. The other is just a text column that will have a formula in each row to store the Idx value.

    So for the other sheet, you take the step value, look up against the step values from the above sheet and return the Idx value for the row that the step belongs to.

    An example would be if the step value in the other sheet was Step 5, then the code would return Idx value 2 from this list.


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What would you want populated if there are multiple Steps selected?

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    edited 01/14/20

    In the other sheet there is only ever one Step value.

    I am just trying to get the Idx value that matches the step from the list above.

    So if the other sheet is Step 5 then return Idx 2. If it is Step 7, return Idx 4.

    I'm trying to find out how to search a multi-select column. I cannot seem to get VLOOKUP or MATCH and INDEX to work. Am I missing something?


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is your screenshot of the sheet you are pulling the data FROM?

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    edited 01/14/20

    The screen shot is that sheet that is a lookup table. It's purpose is to match steps to a stage in a process. The user does not see it.

    The other sheet is the one with the rows in it that the user manages.

    Each row has one step value. The Stage Idx is stored in hidden column.

    All I want to do is based on the step value, search the multi-select column in the lookup sheet and return the Idx value.


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    That worked beautifully.

    I added VALUE around it to convert to a number as I use it in a calculation.

    Thanks Paul for you assistance


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️


    Please don't forget to mark the most appropriate answer(s) as helpful so that others searching for a similar solution know that one can be found here.

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    @Paul Newcome , I found a slight issue in the code

    If there is a word, such as New in more that one of the Steps, eg Step 1 = New and Step 7 = Newcome, then the COLLECT will return both Idx values (1&4->14). So the fix is to replace the CONTAINS in

    =JOIN(COLLECT({Table Sheet Idx Column}, {Table Sheet Steps Column}, CONTAINS([Step Column]@row, @cell)))

    with a HAS

    =JOIN(COLLECT({Table Sheet Idx Column}, {Table Sheet Steps Column}, HAS(@cell,[Step Column]@row)))

    This works as long as there are not two entries with the same step, which should be so in my case.

    Just thought I would update this ticket to help other who might read it.


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!