Does NOT contain formula
Hi everyone. I am trying to come up with a formula that provides a solution to this:
Column "Product" contains a multiselect dropdown with X, Y and Z as options.
Colum "Type" contains a dropdown list with multiple selections available, but needs to reflect "N/A" IF "Product" column as any combination of selections that does NOT include Y and/or Z.
So basically somthing like IF(product@row) NOT("X"), "N/A"
I know that isn't and actual formula but trying to express what I am looking for.
Thanks in advance to the great minds on there that understand formulas way better than I do!🙂
Best Answer

Try something along these lines...
=IF(NOT(CONTAINS("X", Product@row)), "N/A")
Answers

Try something along these lines...
=IF(NOT(CONTAINS("X", Product@row)), "N/A")

Your combination mentioned in text and tentative formula are contradicting so just want to make sure you are looking to have a "N/A" value when Product column does not contain either Y or Z, correct? If yes then try the below,
=IF(NOT(OR(HAS(Product@row, "Y"), HAS(Product@row, "Z"))), "N/A")

@Paul Newcome that was too easy! LOL Thank you.
brings up another question though....is there a way to have the formula allow a drop down selection if Product@row DOES contain "X" or maybe add N/A to the the drop down list and make the previous formula select "N/A" rather than just write it?

@Sameer Karkhanis i think i talked myself into a circle. Ultimately the formula concept I provided was the more correct question. I just wanted to make sure that if Product@row included from the drop down "X, Y" or "X, Z" that it would not result in "N/A"

Ok, in that case try this (In case you want some value to be displayed when the check does not result in N/A then you can add that into the formula below as part of else condition)
=IF(COUNTM(Product@row)>1, IF(HAS(Product@row,"X"), IF(NOT(OR(HAS(Product@row, "Y"), HAS(Product@row, "Z"))), "N/A")))

To output something different if it does contain "X", then you would leverage the third portion of the IF statement.
=IF(NOT(CONTAINS("X", Product@row)), "N/A", this is what happens if it does contain x)
Help Article Resources
Categories
Check out the Formula Handbook template!