# Multiple IF Statement based on Multiple Criteria

✭✭✭
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

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!

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!