Return date after which all values are no less than X

[senor_goose]

Long time reader. First time asker.

I have a table with 2 columns: |Date|Balance|. I'm trying to figure out a function which will take a number in a cell indicated by me, say A1, and return the first date after which the balance will no longer go under that amount.

Date.Balance.xlsx

Thanks in advance.

[AlKey]

It would be easier if you could provide the expected result but try this formula and see if this is what you looking for.

Format cell as Date

=LOOKUP(A1,B4:B2077,A4:A2077)

[Pepe Le Mokko]

Wrong answer by me

[senor_goose]

Say I want to make a purchase of $1000. I would want to see where I can make that transaction without getting a negative balance in the future. Then A1:1000.

So if I'm planning to spend $1000, the earliest I could do that would be 12/4/2015, because after that, the balance won't drop below $1000. I hope that makes sense.

[senor_goose]

I think I figured it out.

{=MAX(IF($B$4:$B$2077<$A$1,$A$4:$A$2077))+1}

Thanks for your time

[AlKey]

Here is without Ctrl+Shift+Enter

=LOOKUP(2,1/(B4:B2077<=A1),A4:A2077)+1

[senor_goose]

Here is without Ctrl+Shift+Enter

=LOOKUP(2,1/(B4:B2077<=A1),A4:A2077)+1

Useful! Just a note on the results of that formula after a little testing: whenever I enter smaller numbers (eg. 100, 200, 300), LOOKUP returns an error.

[AlKey]

Yes, because those values are lover than minimum. Maybe using IFERROR function to return blank?

=IFERROR(LOOKUP(2,1/(B4:B2077<=A1),A4:A2077)+1,"")