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 AFBZCZDZEZGYHYIYJY 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

Replace long nested If with simple formula

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

Common Use Cases

Accepted answers

pedrodrfaria

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

All comments

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

20210330 - DynamicContains.yxmd

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.

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!

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157