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
Check out the Formula Handbook template!