XLOOKUP & SEQUENCE in Dynamic Array

[MrRajKumar]

Hi Everyone,

Here is my data layout (A1;D5)

Date # of Months Amount Pay
1/1/2023 12 1,000 100
1/15/2023 18 1,500 150
2/23/2023 6 2,000 500
6/1/2023 24 2,500 250

All these values are filled as dynamic array. In column D, I need to get in which month we will achieve the amount (multiply # of Months (1 to n) with Pay amount).
So currently I used XLOOKUP with SEQUENCE.

=XLOOKUP(C2,SEQUENCE(,B2)*D2,EOMONTH(A2,SEQUENCE(,B2)-1),"",1)

It works fine. But I have drag down until the data set. So how can I use this formula with dynamic array feature, eg: C2#

May be using with MMULT. I don't know. Thank you for your time & assistance.
Raj

 

[Fluff]

Is each column it's own spill range, or is A2:D5 a single spill range?

 

[MrRajKumar]

Thank you Fluff for your reply.

Each column has its spill.

 

[Fluff]

Missed that you are using 2021 & so don't have Lambda functions.
I'll need to rethink.

 

[MagnarH]

If A1:D5 is from an dynamtic array, then you should just need to add # for each reference.

=XLOOKUP(C2#,SEQUENCE(,B2#)*D2#,EOMONTH(A2#,SEQUENCE(,B2#)-1),"",1)

Or did I missunderstand?

 

[Fluff]

That will never work, sequence does work with an array.

 

[Fluff]

How about

=LET(x,CEILING(C2#/D2#,1),IF(x>B2#,"",EOMONTH(--A2#,x-1)))

 

[MrRajKumar]

If A1:D5 is from an dynamtic array, then you should just need to add # for each reference.

=XLOOKUP(C2#,SEQUENCE(,B2#)*D2#,EOMONTH(A2#,SEQUENCE(,B2#)-1),"",1)

Or did I missunderstand?

Thank you for your reply MagnarH,

That will not work. In D2 SEQUENCE has to create array of 12 months, but in D3 it is 18, D4 is 6 etc...

 

[MrRajKumar]

How about

=LET(x,CEILING(C2#/D2#,1),IF(x>B2#,"",EOMONTH(--A2#,x-1)))

Such a simple logic. Thank you Fluff. Works like a charm!

 

[Fluff]

You're welcome & thanks for the feedback.