Find first number greater than x and return another value in that row

Question

I have created an investment spreadsheet. In one column I have the ending balance each year. In another column I have the years.

 A    B
2000 500K
2001 450K
2003 1.2M
2004 1.5M

How can I display the year I hit a certain arbitrary threshold (say $1M) in a single cell somewhere else on the sheet (say E32), without knowing how long it may take to reach the amount?

Solution

If we can make the assumption (as presented) that years are in column A and amounts are in column B, this array formula will work in any cell that's not in column A or B (such as E32).

=INDEX(A:A,MATCH(INDEX(B:B,MATCH(TRUE,B:B>=1000000,0)),B:B,0))

For array formulas, type it in and CSE - press Ctrl+Shft+Enter. This will display the formula with curly brackets around it.

On OSX, an array formula is input like this-

Select the range, press CONTROL+U and then press ⌘+RETURN.

source

Comments

[Jared]

Ok. This helps and I think will work! But how can I display the FIRST year ONLY in a single cell that isn't in the same row as the correlating info. For example, using your example above since the account hits $1000 in 2002 how could display "2002" in let's just say cell E32? So that there doesn't have to be a whole list, just the first year the account reaches that amount.

[Raystafarian]

@Jared try the new one, it can go in E32 or.. wherever, assuming years are in A:A and money is in B:B

[Jared]

Awesome! That did it!!! Thanks so much!!! I've been trying to figure this out all day!

[Raystafarian]

@Jared if this solves your problem please click the green checkmark next to the answer to mark your question as solved.

[Brandon]

Thanks for this. This question shows up high on Google for the relevant query and exactly solved my problem :)

[Raystafarian]

@Brandon good to hear it!