Nested IF/CONTAINS for Multi-Select Column
Hi. I have a multi-select column in which I have the following formula:
=IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists ACI}), "ACI", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists AppD}), "AppDynamics", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists CCP}), "Cisco Container Platform", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists CSW}), "Cisco Secure Workload", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists CWO}), "Cisco Workload Optimization", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists ConvInfra}), "Converged Infrastructure", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists DPO}), "Data Protection and Observability", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Duo}), "Duo", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists HX}), "Hyperflex", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists IWO}), "IWO", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Intersight}), "Intersight", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Meraki HW}), "Meraki Hardware", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Meraki Lic}), "Meraki Licensing", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Meraki SDW}), "Meraki SD-WAN", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Nexus}), "Nexus", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists SAN}), "SAN", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists SASE}), "SASE", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists SASE Bundle}), "SASE Bundle", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists SDWLic}), "SDWAN Plus Licensing", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists SRW}), "Secure Remote Worker", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Tetration}), "Tetration", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists TE}), "ThousandEyes", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists TrustedWP}), "Trusted Workplace", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists UCS}), "UCS", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists UMB}), "Umbrella", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Vip HW}), "Viptela HW", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Vip SDW}), "Viptela SD-WAN", IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists Webex}), "Webex Suite", ""))))))))))))))))))))))))))))
This formula works inasmuch as it will find the FIRST match and then stop. However, a single entry in the [PF@row] column may meet multiple criteria. I want the formula to identify EVERY match between the PF column value and the ranges.
For example if PF@row = "XYZ" and "XYZ" appears in the {Offer Component Matrix_PFLists ACI} range AND the {Offer Component Matrix_PFlists Trusted WP} range, I want both "ACI" and "Trusted Workplace" to appear in the multi-select column.
I know I need an "OR" in here somewhere, but I can't get the syntax right. Any suggestions? Thanks!
Best Answer
-
You don't want to nest your IF statements. You want to "add" them together.
=IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists ACI}), "ACI") + IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists AppD}), "AppDynamics") + IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists CCP}), "Cisco Container Platform") + IF(CONTAINS(...............
Answers
-
You don't want to nest your IF statements. You want to "add" them together.
=IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists ACI}), "ACI") + IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists AppD}), "AppDynamics") + IF(CONTAINS(PF@row, {Offer Component Matrix_PFlists CCP}), "Cisco Container Platform") + IF(CONTAINS(...............
-
Yep - makes perfect sense. I knew it was going to be something relatively simple. Why is that ALWAYS the thing that stumps me‽‽ Thanks again, @Paul Newcome !
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!