Formula for Calculated SharePoint Column - Return Email Addresses Based on Another Column

Question

Hi,

I am trying to write a formula for a calculated SharePoint column. I would like the formula to return multiple email addresses, based on another column. For example, if City column = "Los Angeles" or "New York," send an email to John Doe and Jane Doe, and in the same formula, if City column = "Miami" or "Houston," send an email to John Smith and Jane Smith. Any help would be greatly appreciated! Thank you!

Answer 1

Hi,

Thank you for the updates.

Based on your example, “if the city is Los Angeles, I would like the email to go to both John Doe AND Jane Doe”, I used the formula below to return two email values for Los Angeles and other cities.

**=IF([City column]="Los Angeles","John ******@email.com;Jane ******@email.com ",IF([City column]="New York","John ******@email.com; Jane ******@email.com ",IF([City column]="Miami","John ******@email.com; Jane ******@email.com ",IF([City column]="Houston","John ****@email.com; **Jane ****@email.com "))))

Note: If Power Automate is sending an email based on the value in the SharePoint List Calculated Column and you can try to place “;” between the two email address in the Formula. For example:

**=IF([City column]="Los Angeles","John ******@email.com;Jane ******@email.com ",IF([City column]="New York","John ******@email.com;Jane ******@email.com ",IF([City column]="Miami","John ******@email.com;Jane ******@email.com ",IF([City column]="Houston","John ******@email.com;Jane ****@email.com "))))

For the number of parenthesis to put, you just count the number of “IF” you used and put the same number of parenthesis at the end.

About the “I would also like to have a formula that says if the city is Los Angeles OR New York OR Miami, email John Doe AND Jane Doe. “, use below formula:

**=IF([City column]="Los Angeles","John ******@email.com;Jane ******@email.com ",IF([City column]="New York","John ******@email.com;Jane ******@email.com ",IF([City column]="Miami"," John ******@email.com;Jane ****@email.com")))

Have a good day.

Regards,
Christophe

Answer 2

Hi Christophe,

Thank you for your help! I am trying to return more than one email for a city. For example, if the city is Los Angeles, I would like the email to go to both John Doe AND Jane Doe. Could you tell me how to do that? Would I just repeat the city, as below, and add another parenthesis at the end of the formula, or is there a way to put "and" between the email addresses, so I don't have to repeat the Los Angeles part? If I do add some type of "and," do I need to add another parenthesis at the end? If I need to add more emails, do I need to add more parenthesis at the end?

**=IF([City column]="Los Angeles","John ******@email.com",IF([City column]="Los Angeles","Jane ****@email.com",

I would also like to have a formula that says if the city is Los Angeles OR New York OR Miami, email John Doe AND Jane Doe.

Thanks!

Answer 3

Hi,

Thank you for your updates.

I did the test in my SharePoint list and would love to show you the steps I used below:

Step 1: Create a City column in my SharePoint list using Add column > Choice and add 4 choices as we have 4 cities.

Step 2: After creating City column, I created a new column (Return email) using Add column > More

![Image](https://learn-attachment.microsoft.com/api/attachments/7fcd3892-65b2-4b76-add2-c4ec7c3c0565?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/dd9ad02e-4f5f-4feb-983a-5b47be2dacff?platform=QnA" rel="ugc nofollow">

You can try the above steps and check the results.

You can provide your list and Power Automate screenshots in case the above steps do not apply to your scenario.

Have a good day.

Regards,

Christophe

Answer 4

Hi Christophe,

Thank you for your reply. I should have mentioned that I do have a Power Automate flow working in conjunction with the SharePoint list to send the emails. I am just looking for a way to get the correct email addresses in the SharePoint column.

Thanks!

Answer 5

Hi,

Welcome in Microsoft community.

We tested your conditions further at our end. Based on the conditions you provided, using calculated formula will only return values based on other columns and will not distribute emails to the users you mentioned.

If you want to distribute emails, you many need to use Power Automate to create a flow based on the provided conditions.

In case I misunderstood your requirements, you can specifically explain in the next reply what you mean by “send an email to” for my better understanding.

Regards,
Christophe