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

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

Is there a way to do this?

Matt Lynn ACT

@nevelev I feel like I'm missing some context. I get what you're asking but what's the output? Are you wanting a "Yes" or "No" if it contains either apples or oranges? Are you wanting it to say "Apples" and/or "Oranges" if either of the two are selected in the one column? Are you wanting to sum or reference a corresponding value that would be on the same row as a potential apple or orange?

Short answer… yes this is very doable.. just need to know the situation more.

Matt Lynn ACT

Yes No OR option:

David011

1. Create a Helper Column: This column will help you identify if the cell contains "Apples" or "Oranges".
2. Use the FIND Function: This function will check if the values "Apples" or "Oranges" are present in the cell.

Here's an example formula you can use in the helper column:

=IF(OR(FIND("Apples", [Your Column]@row) > 0, FIND("Oranges", [Your Column]@row) > 0), "Include", "Exclude")

3. Filter Based on Helper Column: Use the helper column to filter your main column.

If you want to directly return the values "Apples" and "Oranges" in another column, you can use a more complex formula:

=IF(FIND("Apples", [Your Column]@row) > 0, "Apples", "") + IF(FIND("Oranges", [Your Column]@row) > 0, "Oranges", "")

This formula will concatenate the values "Apples" and "Oranges" if they are found in the cell.

I hope this helps!

Matt Lynn ACT

Yes. No. AND option

nevelev

Hi @Matt Lynn ACT -thanks for your comments. 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

Matt Lynn ACT

@nevelev You should put a screenshot of what the result you're looking for looks like based on something you're inputting/selecting to illustrate your request. That being said David's suggestion is also valid but I would include a join() and/or a char(10) as a delimiter so your results show up as separate values also.

nevelev

This is what I am looking for if this helps be more clear…

@Matt Lynn ACT @David011

nevelev

@Matt Lynn ACT @David011 Could either of you support on this above?

Matt Lynn ACT

@nevelev I believe we had a meeting scheduled Monday but you didn't show. I think I need to see what you're trying to do but I've done a few things very similar and think I can help but would like to talk directly. Reschedule a time that you can attend. Thanks,