Complex Excel Formula Required

[vishal1979]

I need an Excel formula that sums quantities from column N, starting from the date specified in cell D4 and continuing until reaching the quantity specified in cell H4. When the target quantity is reached, return the corresponding date from column M in cell I4. Sample excel file attached.

 

[vletm]

vishal1979​

Just confirm that You noticed
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
and still You need something?
Do You have a solution?

 

[p45cal]

One way:

=LET(a,FILTER(M4:N753,M4:M753>=D4),b,SCAN(0,TAKE(a,,-1),LAMBDA(c,d,c+d)),e,XLOOKUP(H4,b,TAKE(a,,1),,1),e)

See cell I7 with that formula.
Cell I4 has the named lambda equivalent with hints as to what belongs in the arguments:

 

[vletm]

vishal1979​

Are You still looking for Your own solution for this challenge?
You asked something complex ...
instead that, could You use something like below?
=INDEX(M:M,MATCH(D4,M:M,0)+ROUND((H4-E4)/N4,0))

 

[Peter Bartholomew]

One way:

=LET(a,FILTER(M4:N753,M4:M753>=D4),b,SCAN(0,TAKE(a,,-1),LAMBDA(c,d,c+d)),e,XLOOKUP(H4,b,TAKE(a,,1),,1),e)

See cell I7 with that formula.
Cell I4 has the named lambda equivalent with hints as to what belongs in the arguments:

View attachment 87035

Hi Pascal
It appears that I have simply matched you step by step

= LET(
    filtered,     FILTER(data, timeline>=startDate),
    accumulated,  SCAN(0, TAKE(filtered,,-1), SUM),
    filteredDate, TAKE(filtered,,1),
    XLOOKUP(target, accumulated, filteredDate,,1)
  )

There is a slight difference because I had an eta reduced lambda available for use. I also fell short of your standards in that I failed to wrap the formula within a final Lambda function to get

= ChangeOutDateλ(data, startDate, target)

 

[Peter Bartholomew]

Hi Pascal
It appears that I have simply matched you step by step

= LET(
    filtered,     FILTER(data, timeline>=startDate),
    accumulated,  SCAN(0, TAKE(filtered,,-1), SUM),
    filteredDate, TAKE(filtered,,1),
    XLOOKUP(target, accumulated, filteredDate,,1)
  )

There is a slight difference because I had an eta reduced lambda available for use. I also fell short of your standards in that I failed to wrap the formula within a final Lambda function to get

= ChangeOutDateλ(data, startDate, target)