Google Sheets display the value of a cell in another cell if certain criteria is met within that row

Question

I am working on an amortization table that allows you to add additional monthly payments in Column G, which adjusts the expected end dates of paying off that loan.

Currently trying to return the value of column C (Dates), in a different cell, when the corresponding row has $0 displayed within Column D. For example, I would like "Apr, 2022" displayed in Cell A1 if D34 = $0.

Answer 1

Try this in cell A1:

=ARRAY_CONSTRAIN(FILTER(C:D, D:D<=0, D:D <> ""),1,1)

This should give you the first month where the amount is equal to, or less than, zero.

Answer 2

try this in your A1 cell:

=INDEX(FILTER(C:C, D:D<=0, D:D<>""), 1, )