Multiple conditions to get a pre-defined result

Hi All,

I have three columns here, Objective (Enum), Verticals (EnumList) and Sub Programme (Text).

If i wish to have Sub Programme to have a pre-defined result D, when Objective = A and Verticals = B and Verticals = C, is there any way for me to go about doing this?

HI @Wee_Chin_Lim

Yes, you could use the SWITCH, IF, AND, or OR Expressions.
Some examples here:

AND({condition 1},{condition 2},{condition 3},..,{condition n})
OR({condition 1},{condition 2},{condition 3},..,{condition n})
IF(<condition>,<then-expression>,<else-expression>)
IFS(<condition1>,<then-expression1>, <condition2>, <then-expression2>, ...)
SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <default_result>)

One example with your description:

IF(
   AND([Objective]=A,
      OR([Verticals]=B,
         [Verticals]=C
      )
   ),
   "text1",
   "text2"
)

Please have a look here:

1 Like

I seem to get this error, any advice?

Cannot compare List with Text in ([Verticals] = “Creator”)

Yes you are right, this is because you have an enumList Type with Verticals.
Problem is: enumList allows you to pick various item.
If tou set your column to “Enum” type, that will restrains the choice to one item only.
In this case, please replace my formula with

IF(
   AND(ANY([Objective])=A,
      OR(ANY([Verticals])=B,
         ANY([Verticals])=C
      )
   ),
   "text1",
   "text2"
)

If you want to keep enumList Type for Verticals, then prefer

IF(
   AND(ANY([Objective])=A,
      OR(IN(B,[Verticals]),
         IN(C,[Verticals])
      )
   ),
   "text1",
   "text2"
)

Please have a look there for further explanations:

And feel free to type in the search bar some keyWords that may help you, like “list”, in the documentation:

2 Likes

Thanks for the reply, however the error i got was “ANY function is used incorrectly”

What is the type of your [Objective] column ?

Enum

I must have said a mistake.

Can you try without the ANY, then ?

IF(
   AND([Objective]=A,
      OR(IN(B,[Verticals]),
         IN(C,[Verticals])
      )
   ),
   "text1",
   "text2"
)

And if that does not work, can you please provide a capture from the table, and from the formula you wrote ?
Thanks

2 Likes

So I’ve tried without the ANY and it works as seen in the photo here.
Screenshot 2021-04-21 21.23.19

However, based on the second screenshot below, I wish to have an expression where if I select the Objective as “Monetization”, Vertical as “Creator”, the Sub Programme will be “Brand Jam”, and when if with the other Verticals that I have selected are “Creator”, “Music”, or any others, I want the Sub Programme to show “Monetization Workshop” instead.

I hope that is clear! Thank You

1 Like

Glad to hear that worked :slight_smile:

For the rest of your message, it’s very clear.
In that case, please refer to my first answer Multiple conditions to get a pre-defined result - #2 by Aurelien

More specifically, I think the IFS expression would fit your needs.

1 Like

I tried to integrate the solution as IFS expression, but I am unsure as to where went wrong, may I know how should the code be like?

Please have a look here:

Tell us is this is not enough

1 Like


I tried to make this in this manner,

IFS(
[Objective] = “Monetization”, “Test”,
IN(“Creator”,[Verticals]), “Brand Jam”,
IN(“Music”,[Verticals]), “Monetization Workshop”
)

but something is wrong, for when i select Monetization, Sub Programme = Test. I thought if i then selected Creator, Sub Programme will change to Brand Jam, but it didnt work.

You are on the good way :slight_smile:

The first input of each pair must be a condition, and that means you can include as many AND, OR, IF, etc as you want in it.
These are evaluated in a sequential way, which means the first condition that returns a Yes will stop the evaluation and give the result.
In your case, because you had first the condition [Objective] = “Monetization” to return Yes, then the following condition is not evaluated.
You may want to evaluate double conditions, so you should use the AND operator.

Try something like this:

IFS(
   AND([Objective]="Monetization",
       IN(“Creator”,[Verticals])
   ), 
   “Brand Jam”,

   AND([Objective]="Monetization",
         IN(“Musics”,[Verticals])
   ), 
   “Monetization Workshop”,

   otherCondition,
   “otherResult”
)
2 Likes

Screenshot 2021-04-21 22.28.51
In this case, I thought it would work in this manner, where if it hits all 3 conditions it will be give the predefined value. But it doesnt seem to be the case.

you can add a NOT() to avoid the “Music” in the first condition.

Remember that :

These are evaluated in a sequential way , which means the first condition that returns a Yes will stop the evaluation and give the result.

2 Likes

Thank You so much, I have learnt a great deal. If I face any other issues, I’ll drop you a message.

1 Like

You are welcome,
I’m glad you learnt things and managed to do what you wanted
Don’t forget to tick “solution” if that answered your need, in case that may help people in your initial situation :slight_smile:

2 Likes