Calculated Column to Return Email Address

[tomhoffman]

Hello All, I have a SharePoint Document library that has a column called "Manager Responsible" that is a 'Choice' dropdown. I'm trying to create a flow that triggers when a file is created and looks for "Manager Responsible" and sends an email to the Manager Responsible when the document is created. However, the Manager Responsible value is simply a name. The way I'm thinking of addressing this is to create a calculated column that returns an email address is example: Manager Responsible is equal to "Cody Harman" then return "codyh@XXXXX.com. That way I would be able to pull the value of the calculated column "Manager Responsible Email" and inject it into the "Send to" in the email flow.

I need help with writing the formula for the IF OR statement so I can apply it to the calculated column in order to use this column in the flow. 

Any help much appreciated.  Thanks all!

[#2 · Accepted solution]

@tomhoffman Can you just make the 'Manager Responsible' a Person column so that the user has to choose from a full list of actual user accounts? Then you can send emails to them, no problem. I know sometimes users complain having to choose from the entire Active Directory listing (although I really don't understand why).

But failing that, I would try syntax like this:
=IF([Manager Responsible]="John Smith", "john.smith@email.com")

I think you can just put a comma at the end and keep adding new IF statements. You'll just have to make sure you get the number of closing parentheses right at the end. If there are only a small handful of manager names, I guess this would work. But this method is limited and means you'll have to maintain it whenever managers change. Choosing from a Person column would be better!

[#3]

@racrig, thx for the reply. If I use the single example you provided it works fine. However, I can't seem to get it right if I add multiple entries to the list. I tried multiple variations in syntax too. Does it need an 'Or' statement? Thx

[#4]

@tomhoffman  in your SharePoint list make sure you have a column that has the email address of the worker. Then in the flow use an Office 365 Users Get manager (V2) action to get that worker's manager. I'd post up some screenshots but I'm about to go into a meeting.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#5]

Thing is it's not always the actual manager of the document creator that the email needs to go to

[#6]

@tomhoffman This worked for me in a test:

=IF([Manager Responsible]="John Smith","john.smith@email.com",
 IF([Manager Responsible]="Joe Jones","joe.jones@email.com",
 IF([Manager Responsible]="Jane Doe","jane.doe@email.com")))

So commas in between options, and close ALL parentheses at the very end. If I put in a Manager name that isn't accounted for in the calculated Email column, it just plugs the word No in there. But again, I recommend just making people choose from a Person column. Low maintenance for you!