Nested IF with Multiple criteria

LoriLynn Smith

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.

L_123

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

LoriLynn Smith

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)))))))