Skip to main content

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!

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

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 '.

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


This is just what I needed, thank you so much this saved me :grinning_face_with_smiling_eyes:

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"

    )

)

Reply


Terms & Conditions