IF/OR Statement

Lila De Vera

Good morning everyone,

first of all, thank you to this community, you have all been a huge help, as someone who had very limited excel/smartsheet skills, this community has helped a lot!

My organization has several departments and in our office one individual is responsible per department.

Although long, my formula looks like this and surprisingly works (in the real formula there are actual names, I've swapped them for privacy):

=IF([Lead Department]3 = "Education", "Name1", IF([Lead Department]3 = "Economic Development & Training", "Name2", IF([Lead Department]3 = "Finance", "Name3", IF([Lead Department]3 = "Civil Service Commission", "Name3", IF([Lead Department]3 = "Infrastructure", "Name4", IF([Lead Department]3 = "Name4", "Name5", IF([Lead Department]3 = "Justice", "Name6", IF([Lead Department]3 = "Agriculture and Resource Development", "Name6", IF([Lead Department]3 = "Conservation and Climate", "Name7", IF([Lead Department]3 = "Families", "Name7", IF([Lead Department]3 = "Municipal Relations", "Name8", IF([Lead Department]3 = "Sport, Culture and Heritage", "Name8", IF([Lead Department]3 = "Central Services", "Name9", IF([Lead Department]3 = "Indigenous and Northern Relations", "Name10"))))))))))))))

First question: as you can see some individuals are in charge of two departments: IF([Lead Department]3 = "Finance", "Name3", IF([Lead Department]3 = "Civil Service Commission", "Name3". I've tried to use this and input it within the original formula: IF(OR([Lead Department]3 = "Civil Service Commission", [Lead Department]3 = "Finance"), "Name3". It works on its own, but not within this above formula, is this possible?

Second question, i'm not sure what type of formula to use, the above formula is within a sheet that tracks projects. Some projects may have more than one department attached to it. For example, Central Services and Finances. Currently in some projects the individual column would be blank because of the above formula. Is there any way for the formula to populate the field with multiple departments and the appropriate names?

Thank you!

Paul Newcome

The OR statement should work for you as long as you place it properly. Basically you are going to take

OR(..........)

and drop it into the "logical statement" portion of the applicable IF.

=IF([Lead Department]3 = "Education", "Name1", IF(OR([Lead Department]3 = "Economic Development & Training", [Lead Department]3 = "Finance"), "Name2", .....................................

But as you have discovered even this won't work if you have multiple departments listed.

For this I would recommend creating a table. It is easier to manage than a long nested IF formula and it will allow additional flexibility.

Then the formula to pull just one instance of each name based on the departments selected (so the same person isn't listed twice if both of their departments are selected) would be:

=JOIN(DISTINCT(COLLECT([Name Table]:[Name Table], [Department Table]:[Department Table], CONTAINS(@cell, [Departments Selected]@row))), ", ")