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

I have an excel table that looks like this:

``````Date         Original       Actual
11/10/10     15             12
12/10/10     12             10
...
24/10/10     2              1
25/10/10     -2             0
``````

I want to know the date at which the original column becomes negative, 25/10/10 in this example.

I've managed to figure out the value using this formula: `=MAX(IF(D63:D91 <0,D63:D91))` which tells me -2.

I just want it to look at the date column where the original value = -2 and tell me that date, but I can't figure out a way to do this at all!

Anyone have any ideas?

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.