THUNKS

VijayVardhan 

THUNKS

1]  To an extent the answer is 'because that is the way it was implemented'.  In normal use, INDEX is applied to a range and the result is also a range object, be it a row, column or cell.  Once the function is applied to an array, the rules change.  To return a multi-element array, the indices need to be explicit, for example

= INDEX(array, {3;2;1}, 5)

Now Lambda functions are first class objects that may be included as parameters or returned as the result of a formula, the rules seem to have changed again and both row and column parameters are required even if one has to be 1.

2] Moving to the next issue,

= INDEX(sumsArray, 4, 1)

is valid, but it returns a Lambda function (specifically a thunk) and not an array of values.  Lambda functions are only evaluated when the necessary parameters are provided.  In the case of the Thunk, no parameters are required but the evaluation is then triggered by the null parameter string:

= INDEX(sumsArray, 4, 1)()

You could even split the calculation in to two parts

= LET(
    arrϑ, INDEX(sumsArray, 4, 1),
    arrϑ()
  )

if you so chose.

The main limitation that is being addressed by the use of thunks is that (as currently implemented) Lambda helper functions are not capable of returning arrays of arrays without contorted workarounds.  Given that I would define the modern spreadsheet as an environment for manipulating multi-dimensional arrays as 2D arrays of arrays, this is an appalling limitation.  Most of the formulas I write require the array of arrays, quite often at multiple points within a given formula.

Note: Traditional spreadsheets approach the problem differently by working from the cell upwards.  Arrays are displayed by writing 10s of 1000s of individual scalar formulas that reproduce the appearance of the result array without any explicit recognition given to the structure of the data being manipulated.