Help with IF/CONTAINS function

nevelev

Hi!

I am trying to write a formula that would return a subset of values from another column. EX:one column is a multiple drop down that contains "Apples","Oranges", "Asparagus", "Broccoli" and I want my other column to just return "Apples", "Oranges".

So the new column would just display basically a filtered version of "Apples","Oranges", "Asparagus", "Broccoli" so the new column would be a dropdown that could have multiple values as well so it would be "Apples", "Oranges"..so not a yes/no but returning those filtered values based on CONTAINS criteria.

The tough part is I am simplifying - it needs to be a pretty lengthy if // contains to capture all the potential options +multiples so the goal would be if contains "Apples" then it would be "Apples" + whatever other contains are caught like "Oranges", then it would be "Apples", "Oranges" in the new column.

Basically -partially CONTAINS() logic but including multiple values and filtering from the other column.

Is there a way to do this?

jmyzk_cloudsmart_jp

Hi @nevelev

First, we need a list of food and categories like this;

https://app.smartsheet.com/b/publish?EQBCT=2c885c31e7464a0b9fb4613d1942a9a4

I tested a couple of formats, but the one like the above works well.

Then, you can filter the OG with the following formula;

=JOIN(COLLECT({Food}, {Food}, CONTAINS(@cell, OG@row), {Category}, [dont want to use this]@row), CHAR(10))

The formula collects from the {Food} range above the sheet values contained in the OG@row. Then, collect from the result the ones with Category that is the same as the [I don't want to use this]@row.

https://app.smartsheet.com/b/publish?EQBCT=4e56c320bf64463c81fa3fc91db12ab5