Multiple IF Statement based on Multiple Criteria
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!
Best Answer
-
I agree. Using an INDEX/MATCH to pull from a table would be much more efficient.
If you decided to go the route of the long formula though, you would need to adjust your syntax within the OR function. You wouldn't need to repeat the IF statement.
=IF(<strong>OR([Postal Code]@row = 12345, [Postal Code]@row = 54321, [Postal Code]@row = 67890, [Postal Code]@row = 09876)</strong>, "Tech A", "Tech B")
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!
Answers
-
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.
-
I agree. Using an INDEX/MATCH to pull from a table would be much more efficient.
If you decided to go the route of the long formula though, you would need to adjust your syntax within the OR function. You wouldn't need to repeat the IF statement.
=IF(<strong>OR([Postal Code]@row = 12345, [Postal Code]@row = 54321, [Postal Code]@row = 67890, [Postal Code]@row = 09876)</strong>, "Tech A", "Tech B")
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!
-
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.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!