+2If 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
+10Hi @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
+2Hi @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
Thank you Chantal! That did it!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.