Help

Re: I want to return multiple values using If statements

1883 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Monica_Merchant
4 - Data Explorer
4 - Data Explorer

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:

image

image

4 Replies 4

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.

image
image

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:

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,')

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.