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

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?

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

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275395

"Domenic" wrote:


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

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275395

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

"Domenic" wrote:


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

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275395

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:K562 0),0))

What do I need to do to make this work?

Thanks again!

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

...entered using CONTROL+SHIFT+ENTER.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275395

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

"Domenic" wrote:


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

...entered using CONTROL+SHIFT+ENTER.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275395