Forum Discussion
HRECURSE instead of MAKEARRAY, recursing LAMBDA
Apologies for resurrecting an old thread, but as it remains one of the quite scarce sources of valuable information on recursion, I feel it is appropriate to clarify INDEX's context for the sake of future readers who might not be familiar with it.
"..INDEX isn't actually a function but an instruction to Excel's precompiler which can only resolve on spreadsheet ranges."
This statement is only true when using INDEX in its reference form, which requires syntax that is distinct from the better-known array form:
INDEX( (array_1,array_2,...array_n) , row_Index, column_Index, [area_Index])
Where the area index selects which array that INDEX will return the intersection of the row and column indices from. This form of INDEX is unable to accept any parameter where:
1. ISREF(array/range) ==> FALSE
2. 'Some Other Sheet!'<Reference>
Note that the label is somewhat misleading, and should not be confused with the colon form of INDEX which will always return a reference or an error; while the reference form cannot accept virtual arrays, it can create them such as in:
=INDEX(($A$2:$F$11,$I$4:$L$13),{3;5;8},SEQUENCE(,4),{1,1,1,1;2,2,2,2;2,2,2,2})
This example showcases an interesting feature of INDEX in its Reference form; this returns the first four columns of the third row in $A$2:$F$11, and the first four columns of the fifth and eighth row in $I4:$L14, which are two obviously non-contiguous ranges.
An example of what the array form can do that the reference form cannot:
INDEX(sequence(30,5),{1;8;2;5;3;8;8;8},{1,2,3,4,5,1,3,5,2,4})
Which returns the array :
With the reference syntax:
=INDEX((SEQUENCE(30,5),SEQUENCE(30,5)),{1;8;2;5;3;8;8;8},{1,2,3,4,5,1,3,5,2,4},1)
Excel will not let you enter this formula because SEQUENCE is not a reference. For reasons unknown to me, this syntax is still valid:
=INDEX((SEQUENCE(30,5)),{1;8;2;5;3;8;8;8},{1,2,3,4,5,1,3,5,2,4},1).
As we can see from this example, INDEX is clearly not restricted to range only operations.