I want to search a multiselect field to return multiple values

Topic Labels: Formulas
517 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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!


2 Replies 2
7 - App Architect
7 - App Architect

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. 


5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you Chantal!  That did it!