IF FIND AND OR formula?

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!

1 Like

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 :slight_smile:

1 Like

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.

3 Likes

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.