Matching using Multi-select column values

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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.




Best Answer

Answers

  • Paul NewcomePaul 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?

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

  • 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

  • Paul NewcomePaul 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.

Sign In or Register to comment.