Nest If/Or with Contains
So with multiple drop downs I want the HC Froup to result in either one of three returns: backfill, backfill repuposed, or new. I am getting an uparseble error. Following is a snapshot of the attempt
Best Answers
-
Hello @Catherine Shea
The syntax in the CONTAINS functions was in error and caused the unparseable. The next opportunity with the formula was the order of the IFs. An IF formula will advance until the first true statement is found, then the formula stops. Since the CONTAINS function will find any instance of a word, in your original formula the CONTANS with only the word "Backfill" would find your desired cells with the word Backfill as well as in the cells with the words "Backfill Repurposed". To mitigate this, I swapped the order of the IF statements so that the formula stops if Repurposed is found. In the snippet you showed it didn't look like it was necessary to write out the entire "Backfill Repurposed" so lazily I didn't- you can add "backfill repurposed' to the formula if desired.
=IF(CONTAINS("New", [HC Group]@row), "New", IF(CONTAINS("Repurpose", [HC Group]@row), "Backfill Repurposed", IF(CONTAINS("Backfill", [HC Group]@row), "Backfill")))
Will this work for you?
Kelly
-
Thanks Kelly for taking the time for the explanation and the fix on this. I used the exmaple you provided and it indeed was the solution. ☺️
Answers
-
Hello @Catherine Shea
The syntax in the CONTAINS functions was in error and caused the unparseable. The next opportunity with the formula was the order of the IFs. An IF formula will advance until the first true statement is found, then the formula stops. Since the CONTAINS function will find any instance of a word, in your original formula the CONTANS with only the word "Backfill" would find your desired cells with the word Backfill as well as in the cells with the words "Backfill Repurposed". To mitigate this, I swapped the order of the IF statements so that the formula stops if Repurposed is found. In the snippet you showed it didn't look like it was necessary to write out the entire "Backfill Repurposed" so lazily I didn't- you can add "backfill repurposed' to the formula if desired.
=IF(CONTAINS("New", [HC Group]@row), "New", IF(CONTAINS("Repurpose", [HC Group]@row), "Backfill Repurposed", IF(CONTAINS("Backfill", [HC Group]@row), "Backfill")))
Will this work for you?
Kelly
-
Thanks Kelly for taking the time for the explanation and the fix on this. I used the exmaple you provided and it indeed was the solution. ☺️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!