Excel #VALUE! error when selecting a subset from a range using INDEX : INDEX or SEQUENCE based on calculated start, end values

This is an hypothetic sample to show the following Excel odd behavior in several situations, where a subset of a range is selected via INDEX:INDEX or INDEX and SEQUENCE, where start and end positions of the subset of the range or the sequence are calculated via INDEX or FILTER.

The scenario is the following: I have an input range with values: {1;2;3;4} in the range: A1:A4 and I want to sum the range from 2 to 4 with the expected result 9, but to illustrate the point, we are going to 开发者_Go百科calculate such numbers checking even condition. The following formula considers several cases:

=LET(a, A1:A4, even, FILTER(a, MOD(a,2)=0),
    startX, INDEX(even, 1), endX, INDEX(even,2),
    startY, XLOOKUP(2, even, even), endY, FILTER(even, even > startX),
    caseA, IFERROR(SUM(INDEX(a,startX):INDEX(a, endX)), "ERROR"),
    caseB, IFERROR(SUM(INDEX(a, SEQUENCE(endX-startX+1,, startX))), "ERROR"),
    caseC, IFERROR(SUM(INDEX(a,startY):INDEX(a, endY)), "ERROR"),
    caseD, IFERROR(SUM(INDEX(a, SEQUENCE(endY-startY+1,, startY))),"ERROR"),
    VSTACK({"caseA", "caseB", "caseC", "caseD"}, 
      HSTACK(caseA, caseB, caseC, caseD))
  )

It returns an error: #VALUE! or a wrong result. If I wrap FILTER with MIN, which is not really necessary because FILTER returns a single value, it works:

Similarly if I wrap startX, endX with MIN it also works.

Notes:

• Using A2:A4 on every where we use the name a, provides the same result.
• Using IFERROR to prevent VSTACK/HSTACK returns a single value in case of error.

Am I doing something wrong or is it a possible bug?

I think it would be of benefit to both you and those wishing to help you if you constructed simpler examples to illustrate your points. What's more, use of the Evaluate Formula tool might help guide you towards an explanation.

There is no bug here, and the behaviour you are witnessing has always been present in Excel.

With the same entries as yours in A1:A4, I will use the following simplified example to explain what is happening:

=INDEX(H1:H10,FILTER(A1:A4,A1:A4=1))

Looking at the Evaluate Formula tool, you can see that this resolves to:

=INDEX(H1:H10,{1})

which is not technically identical to

=INDEX(H1:H10,1)

and suggests that FILTER is returning an array, albeit one comprising only a single value.

For functions which are being instructed to return a cell value, there is no issue in this array being passed. This is the case with the example above, which will not error.

However, if we extend it to:

=SUM(INDEX(H1:H10,FILTER(A1:A4,A1:A4=1)):INDEX(H1:H10,FILTER(A1:A4,A1:A4=1)))

then, by employing INDEX in this way, we are asking it to return a cell reference, not a cell value, i.e. we are asking

=SUM(INDEX(H1:H10,{1}):INDEX(H1:H10,{1}))

to be interpreted as

=SUM(H1:H1)

though such constructions do not allow arrays.

OFFSET and INDIRECT are two other functions which can return cell references and which exhibit similar behaviour.

=OFFSET(H1,1,0)

and

=INDIRECT("H"&1)

are valid, whereas

=OFFSET(H1,{1},0)

and

=INDIRECT("H"&{1})

are not.

Of course, in all these cases the single-value array can be coerced into a non-array via some function (e.g. SUM, MIN).