I want to search a multiselect field to return multiple values

[Mary_Usen]

If my muliselect field has 25 things in it.
I want to find Apples and return apples in the formula field, and I want to find Pears and return Pears to the formula field and if that multiselect field finds both i want it to return Error and if none i want it to return a blank
thanks for your help!

_Mary

[AirOps]

Hi @Mary_Usen, 

The formula that you would want to use is something like this: 

IF(AND(FIND("Apples", {Multiple Select}),FIND("Pears", {Multiple Select})), "Error",
  IF(FIND("Apples", {Multiple Select}), "Apples",
    IF(FIND("Pears", {Multiple Select}), "Pears")
  )
)

the results appear as follows: 

To add some context to the formula, I used the FIND( ) function to search the multiple select field for the specific string I'm interested in, as long as it appears within the multiple select it will come back with a positive result that I can leverage in my IF statement. To check the condition of both Apples and Pears I use the AND( ) function with two FIND( ) functions nested as my two logicals. If both are true then the IF( ) statement will return true.

To check all the conditions you specified, I used a nested if statement, which essentially just checks the conditions sequentially, so if one returns false, the next IF( ) will be evaluated. This is why I need to check for both Apples and Pears first because if I chose to check the conditions for apples, my formula would not check for Apples and Pears. To return blank if neither is found, I chose not to return a value in my formula in my final if statement if it returns false.

I hope this helps! Let me know if you have any questions. 

Chantal

[mku901]

Thank you Chantal!  That did it!