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:

[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Numbers[/TD]
[TD="class: xl65, width: 64, align: right"]14:36[/TD]
[TD="class: xl65, width: 64, align: right"]14:42[/TD]
[TD="class: xl65, width: 64, align: right"]14:48[/TD]
[TD="class: xl65, width: 64, align: right"]14:54[/TD]
[TD="class: xl65, width: 64, align: right"]15:00[/TD]
[TD="class: xl65, width: 64, align: right"]15:06[/TD]
[TD="class: xl65, width: 64, align: right"]15:12[/TD]
[TD="class: xl65, width: 64, align: right"]15:18[/TD]
[TD="class: xl65, width: 64, align: right"]15:24[/TD]
[TD="class: xl65, width: 64, align: right"]15:30[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Percentage[/TD]
[/TR]
[TR]
[TD="class: xl67"]10[/TD]
[TD="class: xl66, align: right"]0%[/TD]
[TD="class: xl66, align: right"]0%[/TD]
[TD="class: xl66, align: right"]14%[/TD]
[TD="class: xl66, align: right"]29%[/TD]
[TD="class: xl66, align: right"]29%[/TD]
[TD="class: xl66, align: right"]43%[/TD]
[TD="class: xl66, align: right"]86%[/TD]
[TD="class: xl66, align: right"]86%[/TD]
[TD="class: xl66, align: right"]86%[/TD]
[TD="class: xl66, align: right"]100%[/TD]
[TD][/TD]
[TD="class: xl69, align: right"]15:12[/TD]
[TD="class: xl70, align: right"]86%[/TD]
[/TR]
[TR]
[TD="class: xl68"]11[/TD]
[TD="class: xl66, align: right"]0%[/TD]
[TD="class: xl66, align: right"]0%[/TD]
[TD="class: xl66, align: right"]43%[/TD]
[TD="class: xl66, align: right"]71%[/TD]
[TD="class: xl66, align: right"]71%[/TD]
[TD="class: xl66, align: right"]71%[/TD]
[TD="class: xl66, align: right"]71%[/TD]
[TD="class: xl66, align: right"]71%[/TD]
[TD="class: xl66, align: right"]100%[/TD]
[TD="class: xl66, align: right"]100%[/TD]
[TD][/TD]
[TD="class: xl69, align: right"]14:54[/TD]
[TD="class: xl70, align: right"]71%[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 1288"]
<colgroup><col span="13"><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Numbers[/TD]
[TD="align: right"]14:36[/TD]
[TD="align: right"]14:42[/TD]
[TD="align: right"]14:48[/TD]
[TD="align: right"]14:54[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]15:06[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:18[/TD]
[TD="align: right"]15:24[/TD]
[TD="align: right"]15:30[/TD]
[TD][/TD]
[TD]Actual output[/TD]
[TD]Time[/TD]
[TD]%[/TD]
[TD][/TD]
[TD]What I wanted[/TD]
[TD]Time[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]88%[/TD]
[TD="align: right"]88%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD]32[/TD]
[TD="align: right"]3:18:00 PM[/TD]
[TD="align: right"]75%[/TD]
[TD][/TD]
[TD]32[/TD]
[TD="align: right"]3:18:00 PM[/TD]
[TD="align: right"]88%[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD]17[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]17[/TD]
[TD="align: right"]2:48:00 PM[/TD]
[TD="align: right"]67%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]57%[/TD]
[TD="align: right"]71%[/TD]
[TD="align: right"]71%[/TD]
[TD="align: right"]71%[/TD]
[TD="align: right"]86%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD]15[/TD]
[TD="align: right"]3:12:00 PM[/TD]
[TD="align: right"]71%[/TD]
[TD][/TD]
[TD]15[/TD]
[TD="align: right"]3:24:00 PM[/TD]
[TD="align: right"]86%[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 925"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]14:36[/TD]
[TD="class: xl63, width: 64, align: right"]14:42[/TD]
[TD="class: xl63, width: 64, align: right"]14:48[/TD]
[TD="class: xl63, width: 64, align: right"]14:54[/TD]
[TD="class: xl63, width: 64, align: right"]15:00[/TD]
[TD="class: xl63, width: 64, align: right"]15:06[/TD]
[TD="class: xl63, width: 64, align: right"]15:12[/TD]
[TD="class: xl63, width: 64, align: right"]15:18[/TD]
[TD="class: xl63, width: 64, align: right"]15:24[/TD]
[TD="class: xl63, width: 64, align: right"]15:30[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"]Time[/TD]
[TD="class: xl64, width: 64"]%[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]32[/TD]
[TD="class: xl64, align: right"]0.13[/TD]
[TD="class: xl64, align: right"]0.13[/TD]
[TD="class: xl64, align: right"]0.13[/TD]
[TD="class: xl64, align: right"]0.38[/TD]
[TD="class: xl64, align: right"]0.38[/TD]
[TD="class: xl64, align: right"]0.38[/TD]
[TD="class: xl64, align: right"]0.75[/TD]
[TD="class: xl64, align: right"]0.88[/TD]
[TD="class: xl64, align: right"]0.88[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: right"]32[/TD]
[TD="class: xl63, align: right"]15:18[/TD]
[TD="class: xl64, align: right"]0.88[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]17[/TD]
[TD="class: xl64, align: right"]0.33[/TD]
[TD="class: xl64, align: right"]0.33[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: right"]17[/TD]
[TD="class: xl63, align: right"]14:48[/TD]
[TD="class: xl64, align: right"]0.67[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]15[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0.14[/TD]
[TD="class: xl64, align: right"]0.29[/TD]
[TD="class: xl64, align: right"]0.57[/TD]
[TD="class: xl64, align: right"]0.71[/TD]
[TD="class: xl64, align: right"]0.71[/TD]
[TD="class: xl64, align: right"]0.71[/TD]
[TD="class: xl64, align: right"]0.86[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: right"]15[/TD]
[TD="class: xl63, align: right"]15:24[/TD]
[TD="class: xl64, align: right"]0.86[/TD]
[/TR]
</tbody>[/TABLE]


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...

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]0%[/TD]
[TD="class: xl65, width: 64, align: right"]0%[/TD]
[TD="class: xl65, width: 64, align: right"]9%[/TD]
[TD="class: xl65, width: 64, align: right"]45%[/TD]
[TD="class: xl65, width: 64, align: right"]64%[/TD]
[TD="class: xl65, width: 64, align: right"]73%[/TD]
[TD="class: xl65, width: 64, align: right"]82%[/TD]
[TD="class: xl65, width: 64, align: right"]91%[/TD]
[TD="class: xl65, width: 64, align: right"]100%[/TD]
[TD="class: xl65, width: 64, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]11%[/TD]
[TD="class: xl65, align: right"]56%[/TD]
[TD="class: xl65, align: right"]67%[/TD]
[TD="class: xl65, align: right"]78%[/TD]
[TD="class: xl65, align: right"]89%[/TD]
[TD="class: xl65, align: right"]89%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]13%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]63%[/TD]
[TD="class: xl65, align: right"]75%[/TD]
[TD="class: xl65, align: right"]88%[/TD]
[TD="class: xl65, align: right"]88%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]33%[/TD]
[TD="class: xl65, align: right"]33%[/TD]
[TD="class: xl65, align: right"]33%[/TD]
[TD="class: xl65, align: right"]33%[/TD]
[TD="class: xl65, align: right"]67%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]40%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]75%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]29%[/TD]
[TD="class: xl65, align: right"]29%[/TD]
[TD="class: xl65, align: right"]29%[/TD]
[TD="class: xl65, align: right"]43%[/TD]
[TD="class: xl65, align: right"]43%[/TD]
[TD="class: xl65, align: right"]43%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[TD="class: xl65, align: right"]75%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]25%[/TD]
[TD="class: xl65, align: right"]25%[/TD]
[TD="class: xl65, align: right"]75%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]40%[/TD]
[TD="class: xl65, align: right"]80%[/TD]
[TD="class: xl65, align: right"]80%[/TD]
[TD="class: xl65, align: right"]80%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0%[/TD]
[TD="class: xl65, align: right"]17%[/TD]
[TD="class: xl65, align: right"]33%[/TD]
[TD="class: xl65, align: right"]67%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]60%[/TD]
[TD="class: xl65, align: right"]80%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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