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

=INDEX(A1:Z1,MATCH(TRUE,A2:Z2=0,0))


needs to be entered with ctrl + shift & enter, format result as date if the
first row is numerical


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Blackcat" wrote in message
...
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

Peo, You make me feel a rookie!

Regards,
--
AP

"Peo Sjoblom" a écrit dans le message
de news: ...
=INDEX(A1:Z1,MATCH(TRUE,A2:Z2=0,0))


needs to be entered with ctrl + shift & enter, format result as date if
the first row is numerical


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Blackcat" wrote in message
...
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

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
--
AP


"Blackcat" a écrit dans le message de
news: ...
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

Thanks to you both problem solved.

You BOTH make me feel like a rookie!



"Blackcat" wrote:

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