Nested IF OR AND string

Options

I am looking to combine 2 variables supported by an AND statement to look for matches and then result in an output

  1. REGION
  2. AREA
  3. Owner (output on match)

=IF(OR([::IMPACTED REGION(S)]@row = "US", [::IMPACTED REGION(S)]@row = "CAD", AND([::IMPACTED FUNCTIONAL AREA(S)]@row = "Insurance", [::IMPACTED FUNCTIONAL AREA(S)]@row = "Sales"), [=IMPACTED & RESPONSIBLE RISK OWNER(S)]@row, "NoName"))

I want to look for REGION name US or CAD and the AREA name Insurance or Sales. if either US or CAD are in the REGION field AND if either Insurance or Sales are in the AREA Field, an Owner named cell out be the match.


How close or far am i from getting this right? I am getting ? I get Unparsed Error or Incorrect Argument right now.


thanks!


Rick Girard

Best Answers

  • Rick Girard
    Rick Girard ✭✭✭✭✭
    Answer ✓
    Options

    Hello Amanda

    the output im looking for is to match a name to the Region and Area they are assigned.

    the 3 columns would be:

    Region - Area - Name

    US - HR - Jane

    CAD - Marketing - Mark

    LATAM - Ops - Jane

    APAC - Sales - Mark

    so if Region=US or LATAM and Area = HR or LATAM , then Name =Jane.

    Many Names have multiple Regions and Areas associated with them so I'm looking to use the AND OR logic to parse out those matches.

  • Rick Girard
    Rick Girard ✭✭✭✭✭
    Answer ✓
    Options

    Thanks i was able to get what I wanted using the =IF(AND(OR(HAS string that I received in another Question i posted.

Answers

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    Options

    Hi,

    To start, your column names are exactly as you typed correct?

    ::IMPACTED REGION(S) and ::IMPACTED FUNCTIONAL AREA(S)

    If so, you will want to create an AND statement for the two required cells, with the OR statements nested in to each cell, which I've put below. I am a little confused on what you want the output to be though. Do you want it to say "NoName" if the And statement is true (both cells have one of the identified options listed)? And if it is untrue, do you want it left blank?


    =IF(AND((OR([::IMPACTED REGION(S)]@row = "US", [::IMPACTED REGION(S)]@row = "CAD")), (OR([::IMPACTED FUNCTIONAL AREA(S)]@row = "Insurance", [::IMPACTED FUNCTIONAL AREA(S)]@row = "Sales")), "NoName")


    Let me know if that helps out or you have any other questions!

  • Rick Girard
    Rick Girard ✭✭✭✭✭
    Answer ✓
    Options

    Hello Amanda

    the output im looking for is to match a name to the Region and Area they are assigned.

    the 3 columns would be:

    Region - Area - Name

    US - HR - Jane

    CAD - Marketing - Mark

    LATAM - Ops - Jane

    APAC - Sales - Mark

    so if Region=US or LATAM and Area = HR or LATAM , then Name =Jane.

    Many Names have multiple Regions and Areas associated with them so I'm looking to use the AND OR logic to parse out those matches.

  • Rick Girard
    Rick Girard ✭✭✭✭✭
    Answer ✓
    Options

    Thanks i was able to get what I wanted using the =IF(AND(OR(HAS string that I received in another Question i posted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!