Skip to main content

IF Statement Formula with three statements and conditions

  • August 30, 2023
  • 2 replies
  • 42 views
P
Forum|alt.badge.img+2

I have this IF Statement Formula:

 
 
 IF(
  {UNH Facilities}= "UNH Facilities - GH Cleared",
    "🟣", 
    IF({UNH Facilities}= "UNH Facilities - Supplying Items",
      "🟠")
 )
 
but I want to add one more piece that returns both an orange and purple circle if {UNH Facilities} has both "
"UNH Facilities - GH Cleared" and "UNH Facilities - Supplying Items". The field it is pulling from is multiple select, don't know if that matters.

2 replies

D
Forum|alt.badge.img+14

This is really only going to work if you only have these two options because you have to test both possible orders they can occur in within the string. The more options you add, the more possible orders you are going to need to test and the messier the formula will become.

IF({UNH Facilities}= "UNH Facilities - GH Cleared", "🟣", IF({UNH Facilities}= "UNH Facilities - Supplying Items", "🟠", IF(OR({UNH Facilities}= "UNH Facilities - Supplying Items, UNH Facilities - GH Cleared",{UNH Facilities}= "UNH Facilities - GH Cleared, UNH Facilities - Supplying Items"), "🟣🟠", BLANK())) )
P
Forum|alt.badge.img+2
  • PaulComms
  • Author
  • New Participant
  • September 5, 2023

This is really only going to work if you only have these two options because you have to test both possible orders they can occur in within the string. The more options you add, the more possible orders you are going to need to test and the messier the formula will become.

IF({UNH Facilities}= "UNH Facilities - GH Cleared", "🟣", IF({UNH Facilities}= "UNH Facilities - Supplying Items", "🟠", IF(OR({UNH Facilities}= "UNH Facilities - Supplying Items, UNH Facilities - GH Cleared",{UNH Facilities}= "UNH Facilities - GH Cleared, UNH Facilities - Supplying Items"), "🟣🟠", BLANK())) )

Thanks for the suggestion, I really appreciate it! I do only have those options. A colleague came up with this, which seems to work too:

 IF(

  {UNH Facilities}= "UNH Facilities - GH Cleared",

    "🟣", 

    IF({UNH Facilities}= "UNH Facilities - Supplying Items",

      "🟠",

          IF(AND(SEARCH("UNH Facilities - GH Cleared",{UNH Facilities})>0, SEARCH("UNH Facilities - Supplying Items",{UNH Facilities})>0),

                  "🟣 🟠 ")

          )

 )

 

Terms & ConditionsAccessibility statement