If And Contains with multiple conditions

Options

Hello,

I have the following formula that incorporates the If Contains for various brands that only have one condition to be met which is working.

=IF(CONTAINS("Fuel", [Store Type]@row), IF(CONTAINS("BP", [Primary Brand Signage]@row), "BP", IF(CONTAINS("Irving", [Primary Brand Signage]@row), "Irving", IF(CONTAINS("Valero", [Primary Brand Signage]@row), "Valero", IF(CONTAINS("Shell", [Brand Credit Card Network]@row), "Shell", IF(CONTAINS("P66", [Brand Credit Card Network]@row), "P66", IF(CONTAINS("Exxon", [Brand Credit Card Network]@row), "Exxon", IF(CONTAINS("Flash Food", [Primary Brand Signage]@row), "Flash Foods", IF(CONTAINS("Marathon", [Primary Brand Signage]@row), "Marathon", "")))))))))

I have two brands (Chase & Fry's) which have multiple conditions to equal the Stream.I am sure it involves an AND function as well as a NOT function. Just not sure how to put them together.

Chase will be Chase as long as the brand is NOT Irving, Grocery Loyalty is NOT Frys and the Credit Card Network IS Chase Paymentech.

Fry's will be Fry's if the Credit Card Network is Chase Paymentech and Grocery Loyalty IS Fry's

Signed CONTAINS challenged

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sharon Castiglia ,

    For Chase insert this string:

    IF(AND(CONTAINS("Chase", [Primary Brand Signage]@row), FIND("Irving", [Primary Brand Signage]@row)<1, FIND("Fry's", [Grocery Loyalty]@row)<1, CONTAINS("Chase", [credit card networ]@row)),"Chase", ...

    For Fry's:

    IF(AND(CONTAINS("Chase", [credit card network]@row), CONTAINS("Fry's", [Grocery Loyalty]@row)), "Fry's", ....

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sharon Castiglia ,

    For Chase insert this string:

    IF(AND(CONTAINS("Chase", [Primary Brand Signage]@row), FIND("Irving", [Primary Brand Signage]@row)<1, FIND("Fry's", [Grocery Loyalty]@row)<1, CONTAINS("Chase", [credit card networ]@row)),"Chase", ...

    For Fry's:

    IF(AND(CONTAINS("Chase", [credit card network]@row), CONTAINS("Fry's", [Grocery Loyalty]@row)), "Fry's", ....

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭
    Options

    I got an #unparsable

  • Sharon Castiglia
    Sharon Castiglia ✭✭✭✭✭
    Options

    Well I played around with it a little bit and here is the final product:

    =IF(CONTAINS("Fuel", [Store Type]@row), IF(CONTAINS("BP", [Primary Brand Signage]@row), "BP", IF(CONTAINS("Irving", [Primary Brand Signage]@row), "Irving", IF(CONTAINS("Valero", [Primary Brand Signage]@row), "Valero", IF(CONTAINS("Shell", [Brand Credit Card Network]@row), "Shell", IF(CONTAINS("P66", [Brand Credit Card Network]@row), "P66", IF(CONTAINS("Exxon", [Brand Credit Card Network]@row), "Exxon", IF(CONTAINS("Flash Food", [Primary Brand Signage]@row), "Flash Foods", IF(CONTAINS("Marathon", [Primary Brand Signage]@row), "Marathon", IF(AND(CONTAINS("Chase Paymentech", [Brand Credit Card Network]@row), CONTAINS("Fry's", [Brand Loyalty Grocery]@row)), "Fry's", IF(AND([Primary Brand Signage]@row = "Circle K", FIND("Irving", [Primary Brand Signage]@row) < 1, FIND("Fry's", [Brand Loyalty Grocery]@row) < 1, CONTAINS("Chase", [Brand Credit Card Network]@row)), "Chase")))))))))))

    Thank you

    Signed NOT AS CONTAINS CHALLENGED

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Perfect. Glad you figured it out. Happy to help. Please tag an accepted answer to close out the discussion.

    Be Well and Merry Christmas,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!