Getting the value in one column from a formula on another column in excel

Since LOOKUP is no good because it takes an ordered set, that'd be

=INDEX(A63:A91,MATCH(MAX(IF(D63:D91<0,D63:D91)),D63:D91,0))

, entered as an array formula (Ctrl+Shift+Enter) (provided column A has dates).

Unfortunately GSerg solution does not work if there are other negative values greater than -2, for instance -1, further down the table.

To identify the first instance of a negative value (according to the date), and the related date, I'd suggest:

{=MIN(IF(D63:D91<0,A63:A91,""))}

Note that the dates in column A do not need to be sorted.

If instead one needs the very first instance of a negative entry regardless of the date, then I'd suggest:

{=INDIRECT("A"&MIN(IF(D63:D91<0,ROW(63:91),"")))}

or, if the A column needs to be dynamic:

{=INDEX(A$1:A91,MIN(IF(D63:D91<0,ROW(63:91),"")))}

hope this helps.

I would add a hidden column in front of the dates, putting with an if a 1 if the previous value in the original column is positive and the current value in the original column is negative, putting a 0 otherwise.

And perform a vertical lookup on 1 in the new column.

I find that very often a hidden column makes the code much more clearer (in stead of some very long formula). I am a programmer, non-programmers tend to use very long functions making it much harder to maintain.