Help

Need help with formula

Topic Labels: Formulas
Solved
Jump to Solution
440 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Davoies
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello everyone,

I'm fairly new to Airtable and I've been impressed with the level of questions the community has solved.  This is the first time I'm posting a question with the hope someone can help me.  Here is my situation:

In Table A, I have 3 single-select fields called Trade Choice 1, Trade Choice 2 and Trade Choice 3. Each record (applicant) has a Province of Residence.  I have a lookup field to Table B that lists each trade with the specific provinces where the trade is a Red Seal trade in a multiple-select field called Red Seal Province.

I want to add a verification step to confirm when the choices of jobs are entered that they are a Red Seal Trade in the province of residence of the applicant. I would ultimately use condition colour to highlight any choices where the trades don't exist as a Red Seal in the province of residence.

How do I write an IF formula that would say:  IF(Red Seal Provinces from Trade Choice 1, Trade Choice 2 and Trade Choice 3 has any of the Province of Residence, then return "yes", otherwise return "no")  

Could anyone help with a formula that would mean has any of below or suggest a better way to go about this?  Thank you for your expertise, time and dedication to the Airtable community.

IF({Red Seal Provinces Trade Choice 1} has any of {Province of Residence}, "yes", 
OR(IF({Red Seal Provinces Trade Choice 2} has any of {Province of Residence}, "yes"),
OR(IF({Red Seal Provinces Trade Choice 3} has any of {Province of Residence}, "yes", "no" ))

1 Solution

Accepted Solutions
Jason_Hill
6 - Interface Innovator
6 - Interface Innovator

I don't know if I understand it correctly, but based on your last part, I would rewrite it as such;

IF(
  OR(
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 1}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 2}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 3})
  ),
  "yes",
  "no"
)

See Solution in Thread

1 Reply 1
Jason_Hill
6 - Interface Innovator
6 - Interface Innovator

I don't know if I understand it correctly, but based on your last part, I would rewrite it as such;

IF(
  OR(
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 1}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 2}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 3})
  ),
  "yes",
  "no"
)