IF AND and OR statements

[Lili7891]

Hi all, 

Hoping this is a simple one and I am just going data blind, but trying to do a multi row formula to update my "work" column per the sample example below

work	grade	(replaces) work
ABC	Manager
ABC	Boss
ABC	Senior
ABC	Manager
ABC	Trainee
ABC	Junior
DEF	Manager
DEF	Junior

I tried

IF [Work] = "ABC" AND
[Grade] = "Boss" OR
[Grade] = "Senior" OR
[Grade] = "Manager" THEN
"ABC (MANAGEMENT)"
ELSEIF
[Work] = "ABC" AND
[Grade] = "Trainee" OR
[Grade] = "Junior" OR
THEN
"ABC (JUNIORS)"
ELSE [Work]
ENDIF

But it doesn't look like it's working as it seems to be only referencing grade and therefore updating every work column with the result based on grade only and not work. I want the result to look like this

work	grade	(replaces) work
ABC	Manager	ABC (Management)
ABC	Boss	ABC (Management)
ABC	Senior	ABC (Management)
ABC	Manager	ABC (Management)
ABC	Trainee	ABC (Juniors)
ABC	Junior	ABC (Juniors)
DEF	Manager	DEF
DEF	Junior	DEF

But I seem to be getting this

work	grade	(replaces) work
ABC	Manager	ABC (Management)
ABC	Boss	ABC (Management)
ABC	Senior	ABC (Management)
ABC	Manager	ABC (Management)
ABC	Trainee	ABC (Juniors)
ABC	Junior	ABC (Juniors)
DEF	Manager	ABC (Management)
DEF	Junior	ABC (Juniors)

Can't work out how to fix this but the ANDs and ORs always throw me. I think if I switch everything to OR it had the same effect.

[DataNath · Accepted solution]

Hey @Lili7891 - I believe this is what you're looking for?

The key to fixing your existing statement is adding brackets to your if statement - around your 'OR' possibilities - so that these checks are done alongside the [Work] condition, rather than as an alternative, which was previously throwing the expression off.

However, another way of tackling this (shown above), that is also more efficient when you have a long list of 'OR "X" OR "X"....' is using the IN function, whereby you just include a list in the ('A','B','C'...) format.

Keeping your original 'OR' lists, adding the brackets:

[binuacs]

@Lili7891 One way of doing this