Need help with formula

Topic Labels: Formulas
Solved
534 1
cancel
Showing results for
Did you mean:
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
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"
)``````
``````IF(