MATCH for a value over a specific number

[kingspur06]

Hi - is it possible to use the MATCH function to return the first time a value is under 100?

I have the following numbers in a column

-1, -3, -7, -15, -31, -63, -127, 1, 0, -2, -6

I am looking to use the MATCH function to tell me that the 7th entry in the list is when the values drop under 100 but cannot seem to figure out how to do it.

I thought you had to set the Lookup Value to True and then set the Array to A2:A10<100 but each result comes out as TRUE so it just provides a result of 1

Any help would be most appreciated

thanks
Mark

 

[JEC]

=MATCH(TRUE,A1:A9<-100,0)

 

[Kaper]

If you have a current version of Excel* you could use:

=MATCH(VLOOKUP(-100,SORT(A1:A11),1),A1:A11,0)

*Please update your profile to show which version you are using

 

[Kaper]

@JEC how about data like

-1​

-3​

-200​

-15​

-31​

-63​

-127​

1​

 

[JEC]

if he is looking for the entry where it goes below -100 first, then the sort is not needed. Lets hear ; )

 

[Kaper]

Indeed,
I was looking for the position of the largest but smaller than threshord, not for the first in sequence which drops below threshold.
When I am reading post #1 again, your proposition convinces me, so let's hear.

 

[kingspur06]

=MATCH(TRUE,A1:A9<-100,0)

Thank You - this works perfectly

 

[Kaper]

@JEC:
Kudos!
You understood the question better