How can I pull a value from one column based on the values of another column?

Question

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.

user0 · Answer

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

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

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

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

One Answer