When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off."

For example, if the "Amount Owed" dropped below zero at "Year" 1, I would want 1 to appear under "Year Paid Off"

I've tried a few different ways but nothing seems to work. I thought I could just do something like =if(f6:f130<0,c6:c130) but that seems way off.

1 Answer 1


if there will be only one possible negative value try this:

E2: =FILTER(C6:C130;F6:F130 <0)

if there will be more than one possible negative values try this:

E2: =TRANSPOSE(FILTER(C6:C130;F6:F130 <0))

enter image description here

  • 1
    Thank you so much that works great! I went with the second option because there are multiple negatives, which outputted all along row 2. To get rid of the superfluous values I found that you can simply use E2: = index(filter(c6:c130,f6:f130<0),1) to only draw the first value. Jun 19, 2018 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.