Helper cell vs Triple call?

[Calgary_Neil]

Hello, I'm trying to reduce and speed up a workbook. I'm stuck on whether to use a helper cell or three calls within a formula.
The current design calls for 500++ (25+? x 20+?) helper cells, or a cells containing 1500++ of the helper cell formula. (Both have the same number of calls).
If a helper cell is used it would contain XLOOKUP( $B65, MTech[ID], INDIRECT( "MTech["& C65+1 &"]" )) and main cell would return;
1) -4 in helper cell gives -4 in main cell.
2) -2 in helper cell gives -2 in main cell.
3) A positive value in helper, returns C65+1 in main.
Now the -4, -2 are code, but it does require 2 different returns, the positive value can between 0 and 30.
Currently I lean toward IFS with three calls and an Error catch value (unknown at this time), with no helper cell

Now I have looked at IFS (multiple calls to helper formula) and CHOOSE (one call, but positive values DON'T correlate with C65+1)
Is there a function(s) or formula which will do this more effectually, stay with IFS, or switch to a helper design?

Thanks Neil using Excel 365

 

[RoryA]

If you have 365 you could use LET instead of helper cells. I suspect you could avoid INDIRECT by using INDEX and MATCH, which would probably make more of a difference

 

[Calgary_Neil]

Thanks for that. Will try LET (didn't that it was out). Index/Match again losses the Structured Table advantages, but you are right, it may be better than using a dynamic function like INDIRECT.

 

[RoryA]

You can still use structured table references. Something like

XLOOKUP( $B65, MTech[ID], INDEX(Mtech,0,MATCH(""&C65+1,MTech{#Headers],0)))

could replace your INDIRECT function.

 

[Calgary_Neil]

Thanks for that, after three tries I get an answer to the STRUCTRED TABLE question.

End solution, NO helper cells but use
=LET( NxtVal, XLOOKUP( $B65, MTech[ID],INDEX( MTech, 0, MATCH( TEXT( C65+1,"0"), MTech[#Headers], 0))), IFS( NxtVal>0, C65+1, NxtVal=-4, -4, NxtVal=-2, -2, TRUE, -9 ))

Again Thanks