Nested IF with Multiple criteria
I am working on an approval by facility.
The first iteration had one approver list, this is working great.
=IF([Contract Expense Variance]1 < 5001, (VLOOKUP("5K", {Voting List Levels Range 6}, 2, false)), IF(AND([Contract Expense Variance]1 > 5000, [Contract Expense Variance]1 < 15001), (VLOOKUP("15K", {Voting List Levels Range 6}, 2, false)), IF([Contract Expense Variance]1 > 35000, (VLOOKUP("100K", {Voting List Levels Range 6}, 2, false)), (VLOOKUP("35K", {Voting List Levels Range 6}, 2, false)))))
Now I need to add the facility option, I have created two approver lists
I believe this should be a IF(AND statement, but I have not been able to crack it. Any ideas or suggestions would be appreciated.
Answers
-
Notepad is your friend on these.
Removed extraneous parenthesis
Removed Extraneous and statements. On an IF Statement there is no continue, there is a true or false. Once you have started down a branch, the previous criteria has already been checked. Example
=if(A1 < 5, "A is less than 5",if(A1 < 10,"A is less than 10, but greater than 5","A is greater than or equal to 10")
The same concept applies here.
At this point I noticed you a done something quite interesting that proves you understand the concept of my previous point. you return the false of the 35k to be between the 15001 and 35k. Now that the formula is cleaned up, and the extraneous information is gone, try what I have posted below
= IF([Contract Expense Variance]1 < 5001, VLOOKUP("5K", {Voting List Levels Range 6}, 2, false), IF(Contract Expense Variance]1 < 15001, VLOOKUP("15K", {Voting List Levels Range 6}, 2, false), IF([Contract Expense Variance]1 > 35000, VLOOKUP("100K", {Voting List Levels Range 6}, 2, false), VLOOKUP("35K", {Voting List Levels Range 6}, 2, false
-
Thanks!
I ended up with this after I added the facility portion, it works but seems rather long. maybe there is a more direct way to get there?
=IF(Facility1 = "D02 - CNRL", IF([Contract Expense Variance]1 < 5001, (VLOOKUP("5K", {Voting List Levels Range 6}, 2, false)),
IF(AND([Contract Expense Variance]1 > 5000, [Contract Expense Variance]1 < 15001), (VLOOKUP("15K", {Voting List Levels Range 6}, 2, false)),
IF([Contract Expense Variance]1 > 35000, (VLOOKUP("100K", {Voting List Levels Range 6}, 2, false)), (VLOOKUP("35K", {Voting List Levels Range 6}, 2, false))))),
IF(Facility1 = "D08 - Syncrude", IF([Contract Expense Variance]1 < 5001, (VLOOKUP("5K", {D08 Voting List Levels Range 1}, 2, false)),
IF(AND([Contract Expense Variance]1 > 5000, [Contract Expense Variance]1 < 15001), (VLOOKUP("15K", {D08 Voting List Levels Range 1}, 2, false)),
IF([Contract Expense Variance]1 > 35000, (VLOOKUP("100K", {D08 Voting List Levels Range 1}, 2, false)), (VLOOKUP("35K", {D08 Voting List Levels Range 1}, 2, false)))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!