Building a formula for a different named ranges that contain a portion of the formula

[trishcollins]

I built a VERY long formula (25+ if statements), which if typed out completely, is too long and I cannot save the file. This portion of the formula, which will be repeated several times, which changes only to Priority, and Type, does work. I tested this one portion knowing that the row met all the conditions and I got the correct answer. At that point, I had already substituted the named range Condition_2 for the text, and that worked fine.

=IF(AND([@Priority]=2,Condition_2,([@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84))<=(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])))),
[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84),(D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority]))))

The formula correctly returns $21,000

Because the entire formula is SO long , I have taken all the repeatable parts and put each into a named range.

Condition_1: '[@Type]="Line of Credit"
Condition_2: 'AND([@Type]="Mortgage",[@[Cumulative this year]]<=[@Maximum]) 'Ensures that the maximum allowed to contribute over regular payments has not been exceeded
Condition_3: 'D$110-(SUMIFS(D$114:D$119,[Priority],"<"&[@Priority])) 'Sum all the amounts already applied to higher priority items, and then deduct that total from any extra funds to see if there is any leftover to apply
Condition_5: '[@[Current Balance]]-XLOOKUP([@[Extra Payments]],$C$3:$C$84,D$3:D$84) 'Current balance less normal monthly payments

This formula, which replaces ALL the long formula parts with a named ranges for the above, evaluates correctly as "True", but then returns the "text" from Condition_5, rather than the calculated result of Condition 5.

=IF(AND([@Priority]=2,Condition_2,Condition_5<=Condition_3),
Condition_5, Condition_3)

I know this is just syntax, or possible the Indirect function around the xlookup, although I have tried that and it doesn't work, but I cannot seem to figure it out.

Any suggestions?

Trish