[Solved] Nested If function not working

[cashby]

How do i get this nested IF function working?
=IF(E15>$M$15,$N$15,IF(E15>$M$16,$N$16,IF(E15>$M$17,$N$17,IF(E15>$M$18,$N$18,0))))

 

[shamimarita]

Hello Cashby,

Your nested IF is working syntactically; it’s the logic/order that’s wrong. Right now, you test the lowest tier first (M15 = 12,522). Since E15 (66,422 in your sheet) is already greater than 12,522, Excel returns N15 immediately (4%) and never reaches the higher tiers (5%, 6%).

Fix 1: Keep Nested IF, but check from highest to lowest
=IF(E15>$M$17,$N$17,
IF(E15>$M$16,$N$16,
IF(E15>$M$15,$N$15,0)))

(Use the highest threshold first, then move down.)
If you want the threshold itself to qualify, use >= instead of >.

Fix 2 (Best/Cleanest): Use LOOKUP (tiers must be sorted ascending)

If M15:M17 are your tier thresholds in ascending order, this is much simpler:
=LOOKUP(E15,$M$15:$M$18,$N$15:$N$18)

This returns the payout for the largest tier that is ≤ E15 (so 66,422 will return 6%).

Quick checks:

• Make sure M15:M18 are real numbers (not stored as text).
• Keep the tier values in M sorted from smallest → largest (LOOKUP needs that).

 

[JORGE W: ROSERO]

MUY buenas tardes, también podríamos usar nombres y fórmulas comparativas:
Y(E4>=MONT1;E4<MONT2)*PORC1+Y(E4>=MONT2;E4<MONT3)*PORC2+Y(E4>=MONT3;E4<MONT4)*PORC3+Y(E4>=MONT4)*PORC4

 

[shamimarita]

Hello JORGE W. ROSERO,

Thanks for sharing your suggestion. Using a nested IF formula can definitely help handle multiple conditions in this case. Just be careful with the structure and parentheses, since a small mistake there can cause the formula not to work properly.

In many cases, the issue with nested IF formulas happens because:

• A parenthesis is missing
• The logical tests are not ordered correctly
• Or the formula becomes too complex to manage

If the number of conditions is large, another option is to use IFS (in newer Excel versions) or create a lookup table with VLOOKUP/XLOOKUP, which can make the formula easier to maintain.

Appreciate your input in helping solve the problem.

 

[TrevorWestbrook]

Hello Cashby,

Your nested IF is working syntactically; it’s the logic/order that’s wrong. Right now, you test the lowest tier first (M15 = 12,522). Since E15 (66,422 in your sheet) is already greater than 12,522, Excel returns N15 immediately (4%) and never reaches the higher tiers (5%, 6%).

Fix 1: Keep Nested IF, but check from highest to lowest
=IF(E15>$M$17,$N$17,
IF(E15>$M$16,$N$16,
IF(E15>$M$15,$N$15,0)))

(Use the highest threshold first, then move down.)
If you want the threshold itself to qualify, use >= instead of >.

Fix 2 (Best/Cleanest): Use LOOKUP (tiers must be sorted ascending)

If M15:M17 are your tier thresholds in ascending order, this is much simpler:
=LOOKUP(E15,$M$15:$M$18,$N$15:$N$18)

This returns the payout for the real money live casino tier that is ≤ E15 (so 66,422 will return 6%).

Quick checks:

• Make sure M15:M18 are real numbers (not stored as text).
• Keep the tier values in M sorted from smallest → largest (LOOKUP needs that).

Good explanation - the early TRUE short-circuit is what trips most people up with nested IFs. The LOOKUP approach is definitely cleaner for tiered values like this.

 

[shamimarita]

Hello TrevorWestbrook,

Thanks for your feedback. Keep contributing on the ExcelDemy forum to help each other.