Return first row value for first matched criterion

[neil7722]

Hey Guys,
Would really appreciate your help on this.
I have percentages sorted from low to high in rows. They might be repeated many times. My first row contains time slots. I want to return the first matched percentage and corresponding time slot for % greater than 67% and less than 99%
Eg:

Numbers	14:36	14:42	14:48	14:54	15:00	15:06	15:12	15:18	15:24	15:30		Time	Percentage
10	0%	0%	14%	29%	29%	43%	86%	86%	86%	100%		15:12	86%
11	0%	0%	43%	71%	71%	71%	71%	71%	100%	100%		14:54	71%

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

For number 10, my first matching >67% criterion is 86% at 15:12, so I want to return the time 15:12 and % as 86%.
Similarly for 11, 71% occurred first at 14:54 so I want that value to be returned along with the %

Thanks in advanced.

 

[Domenic]

Assuming that A1:N3 contains your posted data, try...

M2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$1:$K$1,MATCH(1,IF(B2:K2>67%,IF(B2:K2<100%,1)),0))

N2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX(B2:K2,MATCH(1,IF(B2:K2>67%,IF(B2:K2<100%,1)),0))

Hope this helps!

 

[neil7722]

Awesome... works like a charm... Thanks a ton... you have been of a great help....

Assuming that A1:N3 contains your posted data, try...

M2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$1:$K$1,MATCH(1,IF(B2:K2>67%,IF(B2:K2<100%,1)),0))

N2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX(B2:K2,MATCH(1,IF(B2:K2>67%,IF(B2:K2<100%,1)),0))

Hope this helps!

 

[Domenic]

You're very welcome. Actually, I just noticed that you did say...

...and less than 99%

...so hopefully you changed the formula accordingly.

 

[neil7722]

Your solution was exactly what I had in mind.. I wanted to have % upto 99%..

My actual goal was to get the 2nd highest % for my entire sheet.. I was struggling with the solution so I observed the data and found that for most of the cases.. my 2nd highest % fell between 68-99%. But there are a few cases where the case is different.

eg:

Numbers	14:36	14:42	14:48	14:54	15:00	15:06	15:12	15:18	15:24	15:30		Actual output	Time	%		What I wanted	Time	%
32	13%	13%	13%	38%	38%	38%	75%	88%	88%	100%		32	3:18:00 PM	75%		32	3:18:00 PM	88%
17	33%	33%	67%	67%	67%	67%	67%	67%	67%	100%		17	#N/A	#N/A		17	2:48:00 PM	67%
15	0%	0%	14%	29%	57%	71%	71%	71%	86%	100%		15	3:12:00 PM	71%		15	3:24:00 PM	86%

<colgroup><col span="13"><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

But I can work with this if we cannot find a solution for the above..

You're very welcome. Actually, I just noticed that you did say...



...so hopefully you changed the formula accordingly.

 

[Domenic]

In that case, we can avoid array formulas, and simply use LARGE and INDEX/MATCH...

A1:O4

	14:36	14:42	14:48	14:54	15:00	15:06	15:12	15:18	15:24	15:30			Time	%
32	0.13	0.13	0.13	0.38	0.38	0.38	0.75	0.88	0.88	1		32	15:18	0.88
17	0.33	0.33	0.67	0.67	0.67	0.67	0.67	0.67	0.67	1		17	14:48	0.67
15	0	0	0.14	0.29	0.57	0.71	0.71	0.71	0.86	1		15	15:24	0.86

<tbody>
</tbody>

O2, copied down:

=LARGE(B2:K2,2)

N2, copied down:

=INDEX($B$1:$K$1,MATCH(O2,B2:K2,0))

Hope this helps!

 

[neil7722]

Yep... this works too... Thank you!! :D
Actually I am glad I have solutions to both the approaches. I can now see which fits better for my analysis.

Thanks a ton. You have been of great help man!

 

[neil7722]

Ohh, there was one problem. If 100% is repeated more than once, as we see in my very first post, 2nd row... my value returns as 100%
Sending you more records for you to understand it...

0%	0%	9%	45%	64%	73%	82%	91%	100%	100%
0%	0%	11%	56%	67%	78%	89%	89%	100%	100%
0%	13%	50%	63%	75%	88%	88%	100%	100%	100%
33%	33%	33%	33%	67%	100%	100%	100%	100%	100%
50%	50%	50%	50%	50%	100%	100%	100%	100%	100%
0%	0%	0%	40%	50%	50%	50%	100%	100%	100%
0%	75%	100%	100%	100%	100%	100%	100%	100%	100%
0%	0%	29%	29%	29%	43%	43%	43%	100%	100%
0%	50%	50%	75%	100%	100%	100%	100%	100%	100%
0%	0%	25%	25%	75%	100%	100%	100%	100%	100%
20%	20%	20%	40%	80%	80%	80%	100%	100%	100%
0%	17%	33%	67%	100%	100%	100%	100%	100%	100%
20%	20%	60%	80%	100%	100%	100%	100%	100%	100%

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Domenic]

For the second largest unique value, try...

O2, copied down:

=LARGE(B2:K2,COUNTIF(B2:K2,LARGE(B2:K2,1))+1)

Hope this helps!

 

[neil7722]

Yep this works too... Also tried this
=MAXIFS(B2:K2,B2:K2,"<1") and worked.. :D