Formula to lookup a date of payment

[InvoicingNoob]

Hello,

I have tons of data that looks like this:

Date	Type	Amount
11/1/2024	Payment	2500
10/1/2024	Invoice	1500
9/1/2024	Invoice	1000
8/1/2024	Payment	3500
7/1/2024	Invoice	3500
6/1/2024	Payment	2500
5/1/2024	Invoice	1000
4/1/2024	Invoice	1000
3/1/2024	Invoice	500

What I wanted to achieve is to get the payment date for each of the invoices. In the example, the invoices dated 10/1 and 9/1 were paid 11/1. Invoice dated 7/1 were paid 8/1. and so on.

Is there a way to have the formula triggered based on the Type? so, if it's payments, it will get the payment date of the row above. See sample of what I wanted to accomplish:

Date	Type	Amount	Payment Date
11/1/2024	Payment	2500	-
10/1/2024	Invoice	1500	11/1/2024
9/1/2024	Invoice	1000	11/1/2024
8/1/2024	Payment	3500
7/1/2024	Invoice	3500	8/1/2024
6/1/2024	Payment	2500
5/1/2024	Invoice	1000	6/1/2024
4/1/2024	Invoice	1000	6/1/2024
3/1/2024	Invoice	500	6/1/2024

 

[Phuoc]

Try this:

Book1
	A	B	C	D
1	Date	Type	Amount	Payment Date
2	11/1/2024	Payment	2500	-
3	10/1/2024	Invoice	1500	11/1/2024
4	9/1/2024	Invoice	1000	11/1/2024
5	8/1/2024	Payment	3500	-
6	7/1/2024	Invoice	3500	8/1/2024
7	6/1/2024	Payment	2500	-
8	5/1/2024	Invoice	1000	6/1/2024
9	4/1/2024	Invoice	1000	6/1/2024
10	3/1/2024	Invoice	500	6/1/2024
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(B2="Payment","-",XLOOKUP("Payment",$B$1:B1,$A$1:A1,"-",0,-1))

 

[InvoicingNoob]

nice! this works! i thought i still need a helper column to achieve this.

 

[InvoicingNoob]

hi again. i encountered a concern.

some of the invoices have staggered payments. see sample below:

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

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. given the formula provided earlier, 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

 

[InvoicingNoob]

up