How do I find the first value in a column less than a number?

[Guest]

What I have is a large number of cells (more than 7 so nested IF isn't an
option) that I need to find the first value that is less than a number. For
example:

41
21
78
24
90
55
31
36
62
10
88
44
etc.

And I want to display the first number under 20 (which is 10). How would I
do this?

 

[Domenic]

=INDEX(A1:A12,MATCH(TRUE,A1:A12<20,0))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps

 

[Guest]

=INDEX(A1:A12,MATCH(TRUE,A1:A12<20,0))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Nevermind. I found it. I fat fingered it when I did my CONTROL+SHIFT+ENTER.
My bad. It worked once I did that. Thanks!

 

[Guest]

One more and I've got it.

I had no problem modifying it to find the first value greater than 80. Now
I I need to do between 20 and 80. Here is the formula I tried but it didn't
like it.

=INDEX(K39:K56,MATCH(TRUE,AND(K39:K56<80,K39:K56>20),0))

What do I need to do to make this work?

Thanks again!

 

[Domenic]

=INDEX(A1:A12,MATCH(1,(A1:A12>20)*(A1:A12<80),0))

...entered using CONTROL+SHIFT+ENTER

 

[Guest]

That did the trick! I'm all set, Domenic. Thanks for your awesome and
timely help!