Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
=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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find all cells with a number and mulitply | Excel Discussion (Misc queries) | |||
find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
how do I find an average number of specific words in a column | New Users to Excel | |||
Number of Column Limitations | Excel Discussion (Misc queries) | |||
How do I multiply Column G Totals by a number and display the res. | Excel Discussion (Misc queries) |