I'm trying to create a formula to find the first positive number in a data set, then return a correlating number assigned to that data. An example is I have 20 years' worth of Net Savings in column A, and their correlating Years in column B.
What formula would I use to return the year that sees the first net savings?
1 Answer
You can use this:
{=INDEX(B:B,MATCH(TRUE,A2:A50000>0,0)+1)}Please note, that you don't have to write {} when putting in the formula, this indicates, that this is an array formula, this means that when you enter the formula you have to submit (normally with enter) with Ctrl+Shift+Enter.
Side note, if you don't have Headers, then change A2 to A1 and delete the +1.
