Selecting first negative number in a row and returning the associated column value

I have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:

Please Login or Register  to view this content.

At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).

Hi,

One way with an array formula. i.e. enter with Ctrl-shift-enter. Assumes data is in A1:L2

Formula:

Please Login or Register  to view this content.

Assuming your dates are in A1:L1, and values are in A2:L2, try this ARRAY formula:

=INDEX(A1:L1,1,MIN(IF(A2:L2<0,A1:L1)))

Remember, it is an array formula, so you must apply it by holding down Ctrl + Shift and pressing Enter... not just Enter.

- Moo

normally entered formula
=index(a1:z1,match(true,index((a2:z2<0),0),0))

Originally Posted by martindwilson

normally entered formula
=index(a1:z1,match(true,index((a2:z2<0),0),0))

I always love a non-Array version of a formula... somehow I always forget the Index within an Index method of doing so. Thanks, Martin!

- Moo