Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 21, 2019 02:59 PM
Hey,
I am having trouble with a formula using multiple IF statements with OR and FIND.
Essentially, what I want to do is assign certain statuses to a single text-based column called “phrases”.
The column contains a variety of food types that are embedded in sentences.
I want the following functions in one formula column:
-If a cell in the “phrases” column contains the word “strawberry”, “grape”, “apple” or “raspberry”, then assign that record “fruit”.
-If a cell in the “phrases” column contains the word “avocado” or “squash”, then assign that record “vegetables”.
-If a cell in the “phrases” column contains the word “rice”, or “quinoa”, then assign that record “grain”.
-If a cell in the “phrases” column contains the word “milk” or “yogurt”, then assign that record “dairy”.
-If a cell in the “phrases” column contains the word “butter”, “olive oil”, or “fat”, then assign that record “oil”.
-If a cell in the “phrases” column contains the word “chickpeas” or “lentils”, then assign that record “peas”.
-If NONE of the conditions are met just leave the cell blank
I have success with the first one (fruits) using the following formula:
IF(
OR(
FIND(
“Strawberry”, {Phrases}
),
FIND(
“Grape”, {Phrases}
),
FIND(
“Apple”, {Phrases}
),
FIND(
“Rasberry”, {Phrases}
)
), “Fruit”,"")
However, I have no idea what the proper format is to add the rest of the conditions (the remaining IF statements with the OR and FIND) to this formula. Any help would be much appreciated- thanks!
Jun 21, 2019 05:05 PM
Welcome to the community, Annalise! :grinning_face_with_big_eyes: The short answer to your question: nest the next IF
function where the “False” result of the previous IF
function belongs.
To use your example, “Fruit” will appear if any of those words are found. That’s the “True” result; i.e. the result if that OR
function is true. For the “False” result, you currently have an empty string. While that works, you can technically leave that out, and Airtable will automatically assume the field should remain blank if the IF
is false.
That “False” result is also where you can nest the next IF function. Here’s an example using the next set of checks on your list, which are for vegetables:
IF(
OR(
FIND(
"Strawberry", {Phrases}
),
FIND(
"Grape", {Phrases}
),
FIND(
"Apple", {Phrases}
),
FIND(
"Rasberry", {Phrases}
)
),
"Fruit",
IF(
OR(
FIND(
"Avocado", {Phrases}
),
FIND(
"Squash", {Phrases}
)
),
"Vegetables"
)
)
To add the next level, put a comma after “Vegetables,” then insert the next IF
function. Lather, rinse, and repeat until all levels are added
Jun 21, 2019 05:06 PM
Would ‘avocado-grape smoothie’ be an acceptable phrase — and, if so, would the desired response be ‘fruit, vegetables’? If so, the answer is different than if you only want ‘fruit’ or ‘vegetables’.
Edit: Oops. @Justin_Barrett beat me to it. His is the solution to the second option I mentioned above, where the answer can be ‘fruit’ or 'vegetables '.
Jun 21, 2019 10:11 PM
This is just what I needed, thank you so much this saved me :grinning_face_with_smiling_eyes:
Apr 19, 2021 09:44 PM
FWIW, this is now a LOT easier thanks to the new(ish) regular expression functions. Using REGEX_MATCH()
, the equivalent to the above formula is:
IF(
REGEX_MATCH({Phrases}, "Strawberry|Grape|Apple|Rasberry"),
"Fruit",
IF(
REGEX_MATCH({Phrases}, "Avocado|Squash"),
"Vegetables"
)
)