Multiple IF Statements (IFS)

[delcarpiosamuel]

Hi, 

I'm trying to use the formula tool to reproduce an If statement from excel but I can't seem to get it right. 

The excel formula is below. 

=IF(LEFT(D430854,1)="4",IF(LEFT(M430854,3)="614","PATCH","PM"),IF(C430854="JRZ","1"&MID(D430854,2,2),IF(C430854="SL","1"&MID(D430854,2,2)&"SL",D430854)))

Excel column D is Alteryx field "Prod Line"

Excel column M is Alteryx field "Name - Remark Explanation (GLEXR)"

Excel column C is Alteryx field "WO Start Site"

What I'm trying to accomplish is:

1) If column D starts with a "4" and if column M starts with "614" then I want the column to be say "Patch" for true and "PM" for false.

2) If column C is "JRZ" then I want the column to say "1" plus the middle characters of column D. (In the screenshot below, column C is "JRZ" and column D is "013" so I want column E to say "113")

3) If the column C is "SL" then I want the column to say "1" plus the middle characters of column D. 

Thank you!

Sam 

[DavidThorpe · Accepted solution]

Hi @delcarpiosamuel \

The attached workflow and contained formula should help you achieve this:

Spoiler

IF [C] IN ('JRZ','SL')
THEN '1' + Substring([D],1,2)

ELSEIF left([D],1) = '4'
AND LEFT([M],3) = '614'
THEN 'Patch'

ELSE 'PM' ENDIF

This first tests the appearance of SL or JRZ in column C and prepends the character '1' to make 013 into 113.

It then tests if the left first character of column D is 4 AND column M begins with 614 and applies 'Patch' or 'PM' accordingly.

I may have missed something, or some other situations in your data so please let me know how you get on!

Thanks 

David 

[delcarpiosamuel]

Hi David, 

Thank you!!

It worked!

I realized however, that I wasn't clear in the last part of the formula. 

I want the formula to tell me if [C] is "SL" to give me '1'+ Substring([D],1,2) and when false to show [D]. 

Your current formula 100% shows when true but how can I add when false to just get [D] when [C] is "SL"?

Below is the excel formula and the blue font part is what I wasn't clear with in the first post. 

=IF(LEFT(D433196,1)="4",IF(LEFT(M433196,3)="614","PATCH","PM"),IF(C433196="JRZ","1"&MID(D433196,2,2),IF(C433196="SL","1"&MID(D433196,2,2)&"SL",D433196)))

Basically I want

1) if [D] starts with '4' then if left 3  [M] is '614' show "Patch" for true and "PM" for false when [D] doesnt start with '4' then 

2) if [C] is 'JRZ' then '1'+ last 2 characters of [D] when true and when [C] isn't 'JRZ' then 

3) if [C] is 'SL' then '1'+ last 2 characters of [D] when true and when [C] isn't 'SL' then

4) [D]

Is there a way to make a formula to show all of that?

Thank you

Sam 

[DavidThorpe · Accepted solution]

Hi @delcarpiosamuel 

Great! I wasn't sure on your 'else' part so took a guess!

Try the following instead:

IF left([D],1) = '4'
AND LEFT([M],3) = '614'
THEN 'Patch'

ELSEIF left([D],1) = '4'
AND LEFT([M],3) != '614'
THEN 'PM'

ELSEIF [C] IN ('JRZ','SL')
THEN '1' + Substring([D],1,2)

ELSE [D] ENDIF

Thanks 

[delcarpiosamuel]

Thank you so much!! It works perfectly