Matching using Multi-select column values

Frank Falco

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.

Paul Newcome

Ah. Understood. I was thinking that the screenshot was of the target sheet.

Let's try a JOIN/COLLECT...

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

This will pull the Idx from the table based on where within the table the data in your [Step Column] on the target sheet is located.

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

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 Newcome

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

Frank Falco

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 Newcome

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

Frank Falco

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.

Paul Newcome

Ah. Understood. I was thinking that the screenshot was of the target sheet.

Let's try a JOIN/COLLECT...

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

This will pull the Idx from the table based on where within the table the data in your [Step Column] on the target sheet is located.

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

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

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