I want to return multiple values using If statements

[Monica_Merchant]

I’m trying to return multiple values in a single cell, based on information from another cell. Cell A is a multi-select cell with potential values of G6, G7, G8. Based on the choices selected, I want Cell B to return an email address to match one or more of the values. So, if G6 and G7 have been selected, I want Cell B to return the values of G6leaders@sch… and also G7leaders@sch…My formula works when only one value is selected, but it returns nothing when multiple values are selected.

IF({Grade Level(s) Involed}=“G6”,“G6leaders@sch…”,IF({Grade Level(s) Involed}=“G7”,“g7leaders@sch…”))

Screenshots:

[Vivid-Squid]

Hi @Monica_Merchant,
The ideal formula for this would be SWITCH(), instead of nested IF().

BUT…

Why put this in a formula?

Your Grades field is already a link to another record. Store the email address in that table along side each grade. Then use a look up field to return the email address. This will make it much easier to maintain down the road.

[ScottWorld]

Hi @Monica_Merchant,

Yes, you should do what @Vivid-Squid said, which is to use linked records & lookup fields.

Since you are new to Airtable, I would highly recommend my free Airtable training course, which covers both of these topics in depth:

LinkedIn

Learning Airtable Online Class | LinkedIn Learning, formerly Lynda.com

Learn the basics of Airtable, a spreadsheet-database platform, including how to create tables, use different types of fields, leverage views, work with records, link tables, and more.

[Alexey_Gusev]

agreed with @Vivid-Squid, but if you still want it to be a formula, that should do the trick

IF(({Grade Level(s) Involed}, SUBSTITUTE(
  CONCATENATE(({Grade Level(s) Involed},'leaders@sch.com'),
  ',','leaders@sch.com,')

[Monica_Merchant]

Thank you so much! I’m still wrapping my head around linked fields, and this answer really helped me gain understanding and worked perfectly. I really appreciate you taking the time to answer my question.