How to Spill a nested IF equation

[PrettyGood_Not Great]

Hi,

I have the following algorithm comprised of mostly nested IF. I am struggling to upgrade this to a 365 solution that will allow the equation to spill.

=IF($D11>=FcstMonth,IF(O$10>=$D11,IF(O$10<=$E11,$F11*O$9/SUM(XLOOKUP($D11,$O$10:$BF$10,$O$9#):XLOOKUP($E11,$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=$E11,$F11*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP($E11,$O$10:$BF$10,$O$9#)))))

I don't expect anyone to come back with a working example to this puppy, just looking for any known approaches to handling the series of nested if. When I set all of the vertical references to spill (i.e. $D11# etc), I get a #VALUE.

 

[PrettyGood_Not Great]

If they are named ranges, then that is the reason. Try it like

=BYROW(D11:F24,LAMBDA(br,IF(INDEX(br,,1)>=FcstMonth,IF(O$10>=INDEX(br,,1),IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(INDEX(br,,1),$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)),""),""),IF(O$10>=FcstMonth,IF(O$10<=INDEX(br,,2),INDEX(br,,3)*O$9/SUM(XLOOKUP(FcstMonth,$O$10:$BF$10,$O$9#):XLOOKUP(INDEX(br,,2),$O$10:$BF$10,$O$9#)))))))

That's It! Stone cold, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.