Replace long nested If with simple formula

[RK13]

Hi Team,

I've to create a new column using the existing data with below condition.

If column A contains B,C,D and E then Column F should be Z

if column A contains H,I,J and K then Column F should be Y

A	F
B	Z
C	Z
D	Z
E	Z
G	Y
H	Y
I	Y
J	Y

Similar multiple condition are there.  I can use nested if, but that will be a long formula. Is there any simplified way/formula for this.

Thanks in advance

[Jonathan-Sherman]

Hi @RK13,

Tough little question! I've managed to do it dynamically with the help of a lookup table of the values you want to check for.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[Jonathan-Sherman]

I've also assumed the field (column A) should contain all of B,C,D, E to produce Z as the outcome? If not just let me know!

[RK13]

Looks like question was bit confusing, I just updated my doubt with more details. [Added same below]

I've to create a new column using the existing data with below condition.

If column A contains B,C,D and E then Column F should be Z

if column A contains H,I,J and K then Column F should be Y

A	F
B	Z
C	Z
D	Z
E	Z
G	Y
H	Y
I	Y
J	Y

Similar multiple condition are there.  I can use nested if, but that will be a long formula. Is there any simplified way/formula for this.

[pedrodrfaria · Accepted solution]

Hi @RK13 

Is this what you are looking for? Created two examples (F1 and F2), see below:

F1 Formula:

if [A] IN ('B','C','D','E') THEN 'Z' ELSEIF
[A] IN ('H','I','J','K') THEN 'Y' ELSE Null() ENDIF

F2 Formula:

if [A] IN ('B','C','D','E') THEN 'Z' ELSE 'Y' ENDIF

[RK13]

This one works, thanks 🙂

[Jonathan-Sherman]

Looks like I got the wrong end on this one! I thought it should contain all of A,B,C,D and E to get the outcome of Z. Nice work @pedrodrfaria using the IN() function!