Starts with and exact match formula required

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!

Qualcat.xlsx

Error Message

Accepted answers

SPetrie

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

All comments

binsell

My current statement but not working way I want it to

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

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

else 'No'

ENDIF

SPetrie

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703