3

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?

1 Answer 1

6

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

6
  • 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.
    – Jared
    Apr 2, 2015 at 18:21
  • @Jared try the new one, it can go in E32 or.. wherever, assuming years are in A:A and money is in B:B Apr 2, 2015 at 18:35
  • Awesome! That did it!!! Thanks so much!!! I've been trying to figure this out all day!
    – Jared
    Apr 2, 2015 at 18:47
  • @Jared if this solves your problem please click the green checkmark next to the answer to mark your question as solved. Apr 2, 2015 at 20:14
  • Thanks for this. This question shows up high on Google for the relevant query and exactly solved my problem :)
    – Brandon
    Sep 24, 2015 at 17:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .