displaying a date associated with a value in another column

Hello all... I have attached a spreadsheet that's sort of like a finances timetable. It shows the ups and downs of a balance throughout the year (including future balances based on projected payments). What I want to do is have a display on the top row that shows what my minimum future balance will be and what date that will occur. I have a cell right now showing the minimum of the column, but I want the range to be only from today forward.

The logic (in my head) would be:

1) find the minimum in column D,

2) compare the date in column A of that same row to today's date and see if it is equal or greater to today,

3) if the date in column A is greater than or equal to today's date, then display the balance and the balance date on the top row,

4) if the date in column A is less than today's date, then find the next lowest minimum balance in column D, repeat the same date check, and continue until the "greater than or equal to today's date" argument is satisfied.

I have no idea if this is possible within the confines of Excel, but if anybody had any ideas or suggestions, they would be most appreciated! Thanks!


Gary

Hi Amatwrestler...

Thanks for a quick reply! I see what the VLOOKUP is doing (makes sense when you have a living example!), but I need to take it one step further and maybe make the range of the VLOOKUP be from today through the end of the year? I'm probably flirting with disaster in trying to compare different types of cell data (absolute vs. formulated), but it's worth asking about, at least. Is that sort of VLOOKUP even possible?

=VLOOKUP(M2,A:D,4) You can change the range to the whole columns by eliminating the numbers. This will account for any futures values that may be placed in these columns. (I think I am understanding your needs correcty?)

If you want the minimum future balance try this formula in N2

=MIN(IF(A2:A1000>M2,D2:D1000))

and then for the corresponding date in O2

=INDEX(A2:A1000,MATCH(1,(D2:D1000=N2)*(A2:A1000>M2),0))

These are both "array formulas" which need to be confirmed with CTRL+SHIFT+ENTER. To do that select cell with formula, press F2 key and then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } will appaer around the formula in the formula bar. See attached

You could try this.

Add another column say M. Input this into say M2 and drag all the way down for any dates you have data entered for. =IF(Today()>B2,"NA",D2)

This will only display account balance for today and future dates.

Then at the bottom of the column say in M50, just do a min formula like =min(M2:M49) - this will give you the minimum balance from today on through the future.

Now to get the date - creat another column say N. In N2 enter this, =IF($M$50=N2,B2,"NA")

- this should only produce one date in the column - the date in which your balance is at its min.

From there at the bottom of column N, just do another min calc in say cell N50 =min(N2:N49)

Now you have your min balance and the associated date displayed in M50 and N50 - obviousily you could do these in any cells.

Hope this helps.

Jon

I just put in the formulas as supplied by DaddyLongLegs... THANKS!!! It works perfectly on my master sheet, which is much longer than the sample. I have "whited out" the formula cells and made them link their results into L1 and M1... exactly what I was looking for! Thanks again!


Gary