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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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