Hi, I am struggling with an multiple IF statement. I have tried many ways and keep getting either Malformed If Statement. I am using the statement below:
IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='MATCHED')
THEN "RECONCILED" ELSE Null()
ENDIF
I also wanted to add a couple of other statements to the formula and haven't attempted this yet as struggling with adding just one IF statement above.
IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='UNMATCHED1')
THEN "UNMATCHED" ELSE Null()
ENDIF
IF Contains([Taxonomy used],'E')
AND if ([Matching Status]='UNMATCHED2')
THEN "MATCHED" ELSE Null()
ENDIF
Any suggestions?
Solved! Go to Solution.
Try:
IF Contains([Taxonomy used],'E') AND [Matching Status]='MATCHED' THEN 'RECONCILED'
ELSEIF Contains([Taxonomy used],'E') AND [Matching Status]='UNMATCHED1' THEN 'UNMATCHED'
ELSEIF Contains([Taxonomy used],'E') AND [Matching Status]='UNMATCHED2' THEN 'MATCHED'
ELSE Null() ENDIF
This should get you what you need.
To elaborate a little more on @MarqueeCrew's answer, the problem with your initial formulas is the second IF.
The IF statement can test multiple things at the same time, so you don't need to have an IF for each of them.
IF Contains([taxonomy used],'E') AND [Matching Status] ='MATCHED' THEN
That will resolve this syntax error.
Thanks all for your help. It works :)
Hi All,
I need some more help please it has come a bit more complicated unfortunately. The above formula worked however I have missed something. There are 2 sides to my trades ( Ours/Trade vs Clients/Allege) and the client side does not have the Taxonomy populated but I need the Taxonomy and match status on both the Trade/Allege sides. So now I need to create 2 new columns "New Unmatched status" & "Taxonomy updated".
New column: Taxonomy Updated - Where Trade ID is the same for both Trade and Allege (column A) if Taxonomy Used is E on the Trade side Taxonomy updated needs to have E for both sides. If Taxonomy used is blank on Trade side then remain blank for both sides so that I can use the formula above as currently only getting one side of the trade with the correct new matched status. I have added a table below.
Sorry for the example table format, I cannot paste a table or picture as it is blocked internally. Any help would be appreciated :)
Trade/Allege Trade ID Matching Status Taxonomy Used Current formula only updates one side Taxonomy Updated New Unmatched status
Trade ABC123 MATCHED E RECONCILED E RECONCILED
Allege ABC123 MATCHED MATCHED E RECONCILED
Trade DEFC123 UNMATCHED1 E UNMATCHED E UNMATCHED
Allege DEF123 UNMATCHED1 UNMATCHED1 E UNMATCHED
Trade GHI123 UNMATCHED2 E MATCHED E MATCHED
Allege GHI123 UNMATCHED2 UNMATCHED2 E MATCHED
Trade JKL123 RECONCILED RECONCILED
Allege JKL123 RECONCILED RECONCILED
Trade MNO123 UNMATCHED UNMATCHED
Allege MNO123 UNMATCHED UNMATCHED
Based on the layout you have below, the Multi-Row Formula tool is going to be your best bet.
If I understand your example properly, here's an example formula to create Taxonomy Updated
IF [Trade/Allege] = 'Trade' Then [Taxonomy Used] ELSEIF [Trade/Allege] = 'Allege' then [Row-1:Taxonomy Updated] ELSE NULL() ENDIF
You could also Group By the Trade ID field to make sure that you won't pull in unexpected values.
Does that help with what you need?
Oh WOW thank you so much, that worked a treat :)