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")
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")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!