Nested IF/CONTAINS for Multi-Select Column

Danielle Arteaga

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!

Paul Newcome

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

Paul Newcome

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

Danielle Arteaga

https://community.smartsheet.com/discussion/comment/303033#Comment_303033

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 !

Paul Newcome

Happy to help. 👍️