Multiple IF Statement based on Multiple Criteria

cmcneillyjci
cmcneillyjci ✭✭✭
edited 12/09/19 in Formulas and Functions

I am currently working on automating an "Assigned To" column based on postal codes. There are multiple postal codes options per individual with three individuals. Is there a way to create a (albeit very long) formula to populate? I'm currently working with 

=IF(OR([Postal Code]@row = "01xxx-09xxx", IF([Postal Code]@row = "10xxx-19xxx", IF([Postal Code]@row = "20xxx-29xxx", IF([Postal Code]@row = "30xxx-33xxx", IF([Postal Code]@row = "37xxx-39xxx", IF([Postal Code]@row = "49xxx", IF([Postal Code]@row = "98xxx", IF([Postal Code]@row = "99xxx", "Tech A", "Tech B"

In theory the formula will continue with each of the 14 drop down postal codes.

 

Thanks in advance!

Tags:

Best Answer

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    I'd recommend using a VLOOKUP or INDEX/MATCH combination instead or do you prefer to use a very long formula?

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • If I use the INDEX/MATCH route - is there a way possible to use variables (such as X) because I have such a large range of numbers or would I need to create a table with every postal code in my use case?

     

    Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Not sure I follow!

    Can you explain further?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need a table that specifies each of the numbers you would otherwise be entering into your IF formula. If you can break down the postal codes into sections such as all that start with 12 go to "tech a" and all that start with 13 go to "tech b", so on and so forth, then you would not need a table containing every single postal code.

     

    Using the x as a variable would also not work in the IF/OR statement either unless the data you are looking at actually has the "x" in it.

     

    If you only need to compare to the left two digits of the postal code, then we would need to specify that using a LEFT function instead of "xxx"

     

    How is the data typed out that you are comparing against?

     

    Are you able to provide some screenshots with sensitive/confidential data hidden, removed, or replaced with "dummy data"? That would GREATLY help us in finding a solution that works for you.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!