Using IF, FUNCTIONS (HAS, CONTAINS, etc.), and OR (for searched fields).

Greetings Team,

I feel like I'm missing something simple on this one, but it's a general problem that I encounter a lot.

For this question, I'm able to construct formulas that Smartsheet accepts, but the current version is long and more difficult to understand/manage from other users. This involves using IF, FUNCTIONS, and OR (for the different searched fields). I'm looking for a more elegant/simple solution.

Current Setup

Currently, the in-scope formulas are structured like this

=IF(OR(CONTAINS((search_for #1), (field range / reference #1)),

CONTAINS((search_for #2), (field range / reference #1)),

CONTAINS((search_for #3), (field range / reference #1))),

value_if_true, value_if_false)

Seen above, I'm using the same function (CONTAINS) and the same reference (#1) multiple times. It's wasteful and generates a lot of fluff, especially when you have multiple nested IF functions and a dozen search criteria.

Goal: It would be more efficient in this example, to have one function (Contains), one reference, and multiple search_for values, if possible.

Example Setup that doesn't work... (But if it did, it would solve my need)

=IF(CONTAINS(OR(search_for #1), (search_for #2), (search_for #3)), (field range / reference #1)),

value_if_true, value_if_false)

How can we make this^ work?

Thank you for your attention.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Genevieve P.

Hi @Michael W.

With the combination of these three specific functions (IF, OR, and CONTAINS) then the way you currently have it set up is the only way that I know of to have the proper syntax.

You can use @cell sometimes to replace a range within an OR function, but not if you're also using CONTAINS.

For example:

=COUNTIF(Range/Reference, OR(@cell = "Value 1", @cell = "Value 2", @cell = "Value 3")

But since you're looking to see if a range Contains a certain value then you would need to spell out each possibility within the OR function, as in your first set-up. Additionally, the way that IF functions are set up, this means that listing a range and putting a comma after it would break the logic statement, which is why I used a COUNTIFS.

From what I know your goal isn't possible, but if it is, this is the sort of puzzle that @Paul Newcome is a wizard at! It may also be helpful to see your actual formula so we can identify if there's another way of simplifying the structure.

Cheers,

Genevieve

Paul Newcome

Thanks for the tag, @Genevieve P. Right off I don't see a way of simplifying unfortunately, but I am definitely curious now. I am going to have to play around when I get some free time to see if I can figure something out. I too have run into this frustration, but it was never enough for me to want to figure something different out.

I'll keep everyone posted on what I find over the weekend (I actually have this one off for a change haha).

Paul Newcome

What is the reason for using the CONTAINS function?

Michael W.

Greetings Team. Thanks for your feedback so far.

In this particular example, the formula is looking for specific text (e.g. 1, 2, or 3) in a multi-select column (e.g. 1-12) on the same row.
1. Thus, I could use CONTAINS or HAS to get the function to perform as intended. HAS would have a slightly different formula structure.
2. In both cases, I'm searching for information from a single reference cell-- not a long range.
The IF is added to return calculations if the desired text is / is not present.

So the slightly more defined structure would look something like..

=IF(OR(CONTAINS("1", [Column Name]@row),
CONTAINS("2", [Column Name]@row),
CONTAINS("3", [Column Name]@row)),
Date_Calculation_if_True, "")

Paul Newcome

Ok. Now let's take a look at it from the other side. How many options are there total? If there are only 4 options, then you could say that if it contains "4" then leave blank, otherwise "date_calculation".

=IF(CONTAINS("4", [Column Name]@row), "", date_calculation)

Could that be a potential solution, or do you have enough variables to make that approach just as bulky if not more?

Michael W.

That's a fair question to ask and a good mindset to keep in mind-- to search for the null instead of the presence-of.

In this example/project, I'm looking for 4 different pieces of text within a multi-select of 20 different pieces of text.

--> Thus, this formula alternative is not a superior solution.

Thank you for your thoughts.

Paul Newcome

Ugh. Of course. Haha. I will fiddle with it a bit over the weekend to see if I can find a more efficient way to do this, and I will let you know if I have any additional questions.

Michael W.

Greetings @Paul Newcome. Did you have a chance to play around with this topic?

Paul Newcome

I haven't been able to figure anything out just yet. I'll keep experimenting though.

Paul Newcome

Are you able to provide more details into your actual process?

You mentioned having 20 different options and a nested IF statement. Also incorporating this OR/CONTAINS leads me to believe that you want to have a specific output for each of the 20 different selections but that some selections could have the same output.

But... You say it is a multi-select, so if it contains "1" which has an output of "A" but also contains "9" which has an output of "C", how would you handle that?

There may be another way to approach this in an entirely different manner that we aren't thinking about.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.