IF FIND AND OR formula?

[Edoardo_Abate]

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!

[Ben_Young1]

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!

[Edoardo_Abate]

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:

[Kamille_Parks]

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.

[Edoardo_Abate]

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

[Alexey_Gusev]

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’
)