Starts with and exact match formula required

[binsell]

Hi All - I'm trying to return 'Yes' or No' based on specific criteria. If my QUALCAT column starts with 'H, I, J or C' or is M0002, M0016 or M0018 then I want to return 'Yes' if none of these, then 'No'. I can't seem to work out how to do 2 formula as one is a start with and one is a exact match and not getting the result I want. I have attached a before and after of my data so you can see what I'm trying to achieve. 

Any help would be very much appreciated :) Thank you! 

[binsell]

My current statement but not working way I want it to 

IF [QUALCAT] IN ('M0002','M0016','M0018') then 'Yes'

OR

if startswith ([QUALCAT],'H','I','J','C') then 'Yes'

else 'No'

ENDIF

[SPetrie · Accepted solution]

You dont need the first "then 'Yes' because the OR statement is also part of the condition to generate a 'Yes'.

Also, startswith has too many fields, its only takes one condition. You have an extra if before it as well.

I would suggest using the left statement and another in statement for that part.

IF [QUALCAT] IN ('M0002','M0016','M0018') OR Left([QUALCAT],1) in ('H','I','J','C') then 'Yes' else 'No' ENDIF

[binsell]

Thank you so much! That makes sense, although i thought I would have to do a 'starts with' as only looking for the first letter of the string? 

[SPetrie]

Youre welcome!.

For StartsWith, it does do that, but its format is StartsWith(String, Target,[case sensitivity]) and the Target is singular. You would need to have it in the form of startswith ([QUALCAT],'H') OR startswith ([QUALCAT],'I') etc. which can get tedious real quick.

The Left([QUALCAT],1) is where we take 1 character starting on the left side of the string and then check if its IN our list of approved letters. Same concept and result as StartsWith, but we get the benefit of multiple options.