Index and match with only positive numbers

[jkjak]

=IF(C5>=0,INDEX(A5:A8,MATCH(TRUE,C5:C8>=0),1),"From LY")
formula on d3

I'm trying to have my formula look for the smallest positive number and match it to column 1. If there are no positive numbers then it returns "from ly". I got it to work but if it matches the ly row it gives you S. Stretch instead of LY

 

[bosco_yip]

Hi,

Your statement: " but if it matches the ly row it gives you S. Stretch instead of LY "

What does it mean?

What is your expected formula result in D3, "S. Stretch" or "LY"?

 

[GraH - Guido]

=IF(C5>=0,INDEX(A5:A8,MATCH(TRUE,C5:C8>=0),1),"From LY")
I got it to work but if it matches the ly row it gives you S. Stretch instead of LY

If you run the formula evaluation you'd notice that =MATCH(TRUE;C5:C8>=0;1) returns 4.

You may try:
=INDEX(A5:A8;MATCH(AGGREGATE(15;6;(C5:C8)/(C5:C8>=0);1);C5:C8;0)) it returns LY
=INDEX(F5:F8;MATCH(AGGREGATE(15;6;(H5:H8)/(H5:H8>=0);1);H5:H8;0)) it returns Stretch
=INDEX(K5:K8;MATCH(AGGREGATE(15;6;(M5:M8)/(M5:M8>=0);1);M5:M8;0)) it returns Plan

 

[pecoflyer]

If you run the formula evaluation you'd notice that =MATCH(TRUE;C5:C8>=0;1) returns 4.

You may try:
=INDEX(A5:A8;MATCH(AGGREGATE(15;6;(C5:C8)/(C5:C8>=0);1);C5:C8;0)) it returns LY
=INDEX(F5:F8;MATCH(AGGREGATE(15;6;(H5:H8)/(H5:H8>=0);1);H5:H8;0)) it returns Stretch
=INDEX(K5:K8;MATCH(AGGREGATE(15;6;(M5:M8)/(M5:M8>=0);1);M5:M8;0)) it returns Plan

@jkjak
Don't forget to replace the semicolons with commas in Guido's solutions

 

[Musupothers]

One is the smallest positive integer.
Zero is neither a positive nor a negative number.
If we only want to find the smallest positive real number, we can enter 1 and 0.000000 (infinite zeros).
In this case, the answer is 1 if asked for the smallest positive integer.