How to find a target value from a list of calculated and ordered values

Please see attachment. I have a list of values, D2:02 that are calculated by adding a monthly contribution found in B2 to the previous month's balance.
Is there a formula/calculation that I can place in B4 that will return the first month that the goal can be obtained? For example, if the goal was $70,000,
then Jun-15 should be returned.

Thank you.

It's a little awkward, but this would do what you want:

Please Login or Register  to view this content.

Breakdown:

You INDEX the entire range. Then you choose row 1 (where the dates are that you want to return).

The MATCH formula chooses the column. Match the value you want (B3) against the numeric range (D2:O2). The third variable determines whether you want 0 | Exact, -1 | Greater than or Equal To, or 1 | Less than or Equal To.

At first, you would think that you want -1. You can do this only if your data is sorted Z-A (December - January with your sample data). You also have to invert the row reference, so that 1 = bottom row, 2 = next row up, and so on.

Assuming that your data has to be in the order presented, we use EDATE to adjust for the difference. The INDEX(MATCH) finds the first value less than what you are looking for. EDATE adds one month to that, based on the logical progression of the data. Feel free to test with different values.

If you do invert your data, running December to January, the formula would look like this:

Please Login or Register  to view this content.

Crooza - Thanks for the advice. I noticed however that the result was always 1 month beyond the actual month that should have been returned. So for example, if I entered 70000, the roundup() solution returns Jul-15, where it should return Jun-15. So I just changed the roundup() to a rounddown(), and the correct date is generated.

Thanks again for your help.

Greg

Tony - Clearly the most elegant solution proposed, although it took some study to clear up the cobwebs regarding arrays. Once that cleared, it looked great!

Thanks for the assistance,
Greg

Kbryant - Thanks for assistance. There was an issue however when the target goal exactly matches the value in row 2. In that case, the next month is returned. For example, if the goal entered is $80,000, then Nov-15 is returned, whereas Oct-15 should be returned. I played around with it for a while by adding an error check for an exact match, and got it to work. Here's the result:
=EDATE(INDEX($D$1:$O$2,1,IFERROR(MATCH($B$3,$D$2:$O$2,0)-1,MATCH($B$3,$D$2:$O$2,1))),1).

Thanks again,
Greg