First Over 0

B

Blackcat

I have two rows of cells, the first row contains a financial year ie 06/07.
The second contains values relating to that year.

I need a formula that will find the cell that contains the first value in
the row that is 0 or greater and prroduce the year as the answer.

eg
06/07 07/08 08/09 09/10 10/11
-15 -10 -10 12 11

In this example the answer would be 09/10

I'm struggling with lookup and match so any help would be greatly appreciated.

Thanks
 
A

Ardus Petus

Assuming years are in A1:E1
and values in A2:E2,

=INDEX(A1:E1,1,MIN(IF(A2:E2>=0,COLUMN(A2:E2))))
Array formula to be validated with Ctrl+Shift+Enter


HTH
 
Top