Formula to get recent payment date

[InvoicingNoob]

Invoice Amount - this is the total amount for the invoice
Payment - the amount paid

in the sample provided, Invoice #1 got an initial payment last 6/1. breakdown is 1000 was paid towards #1, 500 was paid towards #3, and 500 was paid towards #4. the second payment for Invoice #1 was paid 11/1.

Invoice #3 got the second payment in 7/1. is there a way to also get the latest date of the payment? in this case, the output that i want to show should look like this:

Date	Type	Amount	Completed Payment
11/1/2024	Payment	1500	-
10/1/2024	Invoice #1	1500	11/1/2024
9/1/2024	Invoice #2	1000	11/1/2024
8/1/2024	Payment	3000	-
7/1/2024	Invoice #3	3500	8/1/2024
6/1/2024	Payment	2000	-
5/1/2024	Invoice #1	1500	11/1/2024
4/1/2024	Invoice #3	3500	8/1/2024
3/1/2024	Invoice #4	500	6/1/2024

 

[Cubist]

Something is missing in the post. What you're describing does not what is shown in the table. Can you explain more by picking a specific example?

 

[InvoicingNoob]

of course... had to correct the table as well. apologies.

i hope i can better explain this.

we issued Invoice #1 5/1 (in bold). in the example below, the first payment for this invoice was sent 6/1 (colored blue). then, the second payment to complete the invoice was sent 11/1 (colored red).

i had this initial formula set in D2 =IF(B2="Payment","-",XLOOKUP("Payment",$B$1:B1,$A$1:A1,"-",0,-1)). what this does is it captures the payment date of the payment above the invoice.

also in the example, the items below each payment is/are the invoice/s where the payment was allocated. for example, the payment last 11/1 was allocated to Invoice #1 and Invoice #2.

so, instead of just getting the payment date above the invoices, i want to get the most recent payment date. in my database, we have several instances of invoices being paid partially.

i hope this explains it.

Date	Type	Amount	Completed Payment
11/1/2024	Payment	1500	-
5/1/2024	Invoice #1	1500	11/1/2024
9/1/2024	Invoice #2	1000	11/1/2024
8/1/2024	Payment	3000	-
4/1/2024	Invoice #3	3500	8/1/2024
6/1/2024	Payment	2000	-
5/1/2024	Invoice #1	1500	11/1/2024
4/1/2024	Invoice #3	3500	8/1/2024
3/1/2024	Invoice #4	500	6/1/2024

 

[Cubist]

Is this what you're looking for?

Book1
	A	B	C	D	E	F	G
1	Date	Type	Amount	Completed Payment		Invoice	Latest Payment Date
2	11/1/2024	Payment	1500	-		Invoice #1	11/1/2024
3	5/1/2024	Invoice #1	1500	11/1/2024		Invoice #2	11/1/2024
4	9/1/2024	Invoice #2	1000	11/1/2024		Invoice #3	8/1/2024
5	8/1/2024	Payment	3000	-		Invoice #4	6/1/2024
6	4/1/2024	Invoice #3	3500	8/1/2024
7	6/1/2024	Payment	2000	-
8	5/1/2024	Invoice #1	1500	11/1/2024
9	4/1/2024	Invoice #3	3500	8/1/2024
10	3/1/2024	Invoice #4	500	6/1/2024
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=XLOOKUP(1,($B$2:$B$10="Payment")*(SEQUENCE(ROWS($A$2:$A$10))<XMATCH(F2,$B$2:$B$10)),$A$2:$A$10,,,-1)

 

[InvoicingNoob]

this works. it takes time to load but this works. thanks for your help!