Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

IF FIND AND OR formula?

Topic Labels: Formulas
365 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello! Do you guys know if it’s possible to create a formula that return a rate (1 to 3) IF:

  • in the field {name field}, there are both “option1” AND “option2” = 3
  • in the field {name field}, there is only “option1” = 2
  • in the field {name field}, there are no “option1” OR “option2” = 1

I tried to use IF, FIND, AND, OR but I couldn’t do it :frowning:

Thanks for support!

5 Replies 5

Hey @Edoardo_Abate!
Welcome in!

So, I presume you’re using a multi-select field.
If you are, then this formula format will work with the parameters you provided:

IF(
    {Field},
    IF(
        {Field} = "option1, option2",
        3,
        IF(
            {Field} = "option1",
            2
        )
    ),
    1
)

Truth be told, multi-select fields are an absolute pain to work with sometimes.
Here, Airtable treats the multi-select values as a string containing the comma-separated values.

Lemme know if you have any questions or run into any issues!

Thank you Ben!!
It works, I’v just modified like this:

IF(
  AND(
  FIND("option1",{field}),
  FIND("option2",{field})
  ),"5",
	IF(
	OR(
	FIND("option1",{field}),
	FIND("option2",{field})
	),"4",
    		IF(
      		AND(
       		FIND("option2",{field}), 
        	FIND("option3",{field})
        	),"3",
      			IF(
          		AND(
            		FIND("option3",{field}),
            		FIND("option4",{field})
          		), "1", "2"
      )
    )
  )
)

Thanks a lot :slightly_smiling_face:

The OR(option1, option2) condition should be your last condition checked, or should at least be after the AND(option2, option 3) condition. Your formula as written will never check if both 2 and 3 are present since it stops running once 2 is found.

You’re right @Kamille_Parks!! I really appreciated your support guys, now everything works correctly :star_struck:
Thanks again

Hi,
Depending on your final goal, you can also use variations of:

SWITCH(
8*(FIND(“option1”,{field})>0)+
4*(FIND(“option2”,{field})>0)+
2*(FIND(“option3”,{field})>0)+
(FIND(“option4”,{field})>0),
0,‘empty cell’,
1,‘just option4’,
2,‘just option3’,
3,‘option 3 and 4 together’,

15,‘all are present’,
‘some default’
)