Loop through table column with spill formula

MichielS340 

I have tidied my previous solution up a bit by modifying my MAPλ Lambda (helper) function to accept the 3 input arrays in addition to the user-provided Lambda function that does the work.  My worksheet formula reduces to

= MAPλ(Table2[Begin], Table2[Eind], Table2[Hours], UurPerDagλ)

where the user Lambda function is given by

UurPerDagλ
= LAMBDA(StartDag, EindDag, UurPerDag,
    LET(
      NettoWerkdagen, NETWORKDAYS(StartDag, EindDag),
      LijstWerkdagen, WORKDAY(StartDag - 1, SEQUENCE(NettoWerkdagen)),
      UurPerDag,      AantalUur / NettoWerkdagen,
      return,         HSTACK(
                        LijstWerkdagen, 
                        EXPAND(UurPerDag, NettoWerkdagen, , UurPerDag)),
      return
    )
  )

(I have little idea whether this is the intended calculation).

Like the other solutions, this one uses thunks, so building an array of functions, rather than an array of arrays which causes Excel to error.  A key feature of the approach is that the thunks are formed without involving the user; the user provides a perfectly standard Lambda function that performs the calculation for a single cell from each of the input arrays. 

Something else that would be important if 1,000s of outputs were required, is that the stacking of calculated thunks is performed by assembling the result arrays as a binary tree rather than relying of REDUCE/VSTACK.  This can be 10 or 20 times faster for large dimension input arrays as well as concealing the complications from the end user.

The updated gist is to be found at

A version of Excel MAP helper function that will return an array of arrays (github.com)

You are welcome to look at it, but the intention is that it may be used without inspecting the content, since the expertise available within this discussion is far from 'normal'.

MichielS340 

As variant

=LET(
 start, MIN( INT(Table2[Begin]) ),
 end,   MAX( INT(Table2[Eind]) ),
 days,  SEQUENCE(, end-start+1, start ),
 check, IF( (days >= INT(Table2[Begin]))*(days <= INT(Table2[Eind])), TRUE ),
 VSTACK( days, check )
)

and Insert->Checkbox on the top (if you are on Current channel)

MichielS340 

Patrick2788 approach with MAP:

=LET(
  thunk,  LAMBDA(x, LAMBDA(x)),
  thunks, LAMBDA(begin,eind,hours,
    LET(
      NettoWerkdagen, NETWORKDAYS(begin, eind),
      LijstWerkdagen, WORKDAY(begin - 1, SEQUENCE(NettoWerkdagen)),
      UurPerDag,      hours / NettoWerkdagen,
      thunk(
        HSTACK(
          LijstWerkdagen,
          EXPAND(UurPerDag, NettoWerkdagen, , UurPerDag)
        )
      )
    )
  ),
  Mapped,   MAP(Table2[Begin],Table2[Eind],Table2[Hours], thunks),
  StkThks,  LAMBDA(acc,rw, VSTACK(acc, INDEX(Mapped, rw, 1)()) ),
  DROP( REDUCE(0, SEQUENCE(ROWS(Table2)), StkThks), 1)
)

MichielS340 

The problem arises on the line

    LET(
        ...
        LijstWerkdagen, WORKDAY(StartDag - 1, SEQUENCE(NettoWerkdagen)),

where SEQUENCE( ) generates an array of days which BYROW( ) then attempts to assemble into an array of arrays.  While I was working through the formula, I downloaded a Lambda (helper) function 'MAPλ' that I only finished writing last week (maybe some checking required).

That would at least allow you to look at numbers rather than error messages.