SEQUENCE in a LET formula not behaving as expected

[p45cal]

In exploring a formula-only solution to this this thread: https://chandoo.org/forum/threads/vlookup.50070/ I've come across an unexpected block.
To break the problem down into smaller steps I've started with the likes of
38067-38098
in a single cell (a string, not a calculation) and I was aiming to get 38067,38068,38069,38070,38071,38072 etc. in a column of cells.
So in the attached I put that string in cell A1. I've got the result I want in cell Q2 and spill range, but it uses helper cells.
In the attached, the problem formula is in cell D1; I was expecting output as in C1 but I only get a single number. What the f am I (or Excel) doing wrong?!
SEQUENCE isn't working. =LET(d,31,SEQUENCE(d)) works fine, why not in cell D1?
To debug I've put the same LET formula in cell K2:N2 with different final arguments to make sure I'm getting the expected results.

Of course, I might be going around the houses with this formula and there'll likely be more elegant formulae and would like to see them, but I'd still be interested to know why I'm not getting the expected result. I really don't expect to get a decent solution for Ram Chandran's thread unless I look to making lambda formulae, it might even encourage me to look at recursive lambdas, but I doubt I'll get the time. It's become more academic for me to know what I've done wrong.

If I mention @Peter Bartholomew perhaps he'll jump in with some comments?

 

[Fluff13]

Excel sees both b & c as an array, which sequence doesn't like.
One option is to use
=LET(a,VALUE(TEXTSPLIT(A1,,"-")),b,INDEX(a,1,1),c,INDEX(a,2,1),d,c-b,SEQUENCE(d))

 

[John Jairo V]

Hi to all!

Nice catch by @Fluff13!

Another way to do it:
=LET(a,VALUE(TEXTSPLIT(A1,,"-")),b,INDEX(a,1),c,INDEX(a,2),d,c-b,SEQUENCE(MAX(d)))

The aggregation functions (like MAX, MIN, SUM, etc.) convert the "array part" to a number. Like this, sequence can work normally. Blessings!

 

[Peter Bartholomew]

Seems that @Fluff13 has sorted the problem. It appears that the INDEX formula has returned arrays

= TYPE(a) gives 64
= TYPE(b) gives 64

Modifying the INDEX formula to avoid defaulting the column index would return scalar values, so

= LET(
    a, VALUE(TEXTSPLIT(A2, , "-")),
    b, INDEX(a, 1, 1),
    c, INDEX(a, 2, 1),
    d, c - b,
    s, SEQUENCE(d),
    s
  )

works. An alternative is to allow 'd' to be calculated as an array but then use "@" to truncate the (1x1) array

= LET(
    a, VALUE(TEXTSPLIT(A2, , "-")),
    b, INDEX(a, 1),
    c, INDEX(a, 2),
    d, c - b,
    s, SEQUENCE(@d),
    s
  )

Would I have fallen into the trap? Quite possibly!

 

[Peter Bartholomew]

I have just had a further thought; expanding the string as a row also works with a single index.

= LET(
    a, VALUE(TEXTSPLIT(A2,"-")),
    b, INDEX(a, 1),
    c, INDEX(a, 2),
    d, c - b,
    s, SEQUENCE(d),
    TYPE(c)
  )

 

[p45cal]

Thank you all very much for your inputs. I've learned something.

 

[Fluff13]

Glad to help & thanks for the feedback.

Would I have fallen into the trap? Quite possibly!

I already have, which is why I knew about it.

 

[GraH - Guido]

Thank you all very much for your inputs. I've learned something.

I already have, which is why I knew about it.

Comforting to know, aces do struggle too...