Formula issue; finding a figure

[mikegibson]

i cant get my head round this one:

Period (Year) 15 15 15 15 15
Years Remaining 0 3 15 15 15

i have a 5 cells with figures (years) that vary, but normally will b
the same (in this case 15)
Below each one is a figure, which is the amount of years remaining fro
the total.
I would like a formula which finds the cell with remaining years. I
this case it would be the 3 in cell C2.


Period (Year) 33 33 33 33 33
Years Remaining 0 0 5 15 15

Here i would like it to find the 5 in cell D2.


Any help would be greatly appreciated.
Thanks, Mike

 

[Luke M]

Perhaps something like the array** formula:

=MIN(IF(B2:F2>0,B2:F2))

is what you're looking for.
**Use Ctrl+Shift+Enter to confirm formula, not just Enter.

 

[Don Guillett]

try this
=INDEX(I2:M2,1,MATCH(1,I2:M2,1)+1)

 

[mikegibson]

Many thanks to both of you.

Luke, thats something like i had, but i just couldnt get it to work!
Don, spot on! Worked a treat.

Thanks

 

[Pecoflyer]

Glad we could be of help

--
Pecoflye

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html
really IS fast