IF Statement based on multi-value drop-down

ryan.diamond55371

Hello All,

I have an IF statement that needs to execute a formula if True, and present a statement if False, typical I know. But I need this to work based on the multiple values that can be selected in an adjacent cell. I've tried IF CONTAINS or IF HAS and have not had success. Let me know if anyone has encountered this before

bisaacs

Hey @ryan.diamond55371,

To do this, you'll want it to look something like this:

=IF(OR(CONTAINS("Dropdown Choice 1", [Dropdown Column]:[Dropdown Column]), CONTAINS("Dropdown Choice 2",[Dropdown Column]:[Dropdown Column])),"True", "False")

Hope this helps!

ryan.diamond55371

@bisaacs How would the formula look in the "True" position? I have an Index/Match formula in that position but I'm getting a #INVALID DATA TYPE error

bisaacs

Hey @ryan.diamond55371,

To clarify, do you want the logical expression for the IF statement to be based on what the dropdown contains (like the formula I gave in my previous post), or are you wanting to use INDEX/MATCH as the logical expression for the IF statement?

Either way, since INVALID DATA TYPE ERROR could be triggered by a number of things, I'd cut the formula down to just the IF statement with the logical expression, then if true return "True". If it returns an error then there's something wrong with your logic expression. If it returns True (or blank), then there's something wrong with the next expression you're trying to run.

ryan.diamond55371

@bisaacs After sending my comment I realized my mistake, Missing brackets, and was able to get the formula to work. My issue now is that I am receiving the #NO MATCH error from my Index/Match formula instead of the "No Scope" message that is my False Statement. The formula should not run if it does not contain that values I set in the beginning of the IF statement?

bisaacs

Hey @ryan.diamond55371,

If you're using an OR function in the IF statement, then it'll return true if it contains either options presented. If you only want it to return true (thus running the INDEX/MATCH formula), then I think changing the OR to AND should fix that issue.

Hope this helps!

ryan.diamond55371

@bisaacs There are a total of 8 options that can be selected, I only put 3 of them in the formula so far but the option I selected to test is not in the formula so it should return the False stated correct?

bisaacs

@ryan.diamond55371 that's correct, if it's not an option in the OR/CONTAINS logic then it should return False.

ryan.diamond55371

@bisaacs It is not working then because it is running the formula when it should not