Finding the first number in a list that meets specific criteria

[seh27]

I have a column of 20 random numbers. The first 8 numbers, lets call them A, are considered their own group. The last 12 numbers, lets call them B, is the list I am search through. I am trying to find the first number of list B that is greater than the maximum number of list A. I was going to have embeded If functions, but it would exceed the available space for the formula. I have searched through and found some examples using Index and Match but I can't figure it out for my case. Does anyone have any suggestions? Thanks!

Here is an example of the column:
(Bold numbers is list A)

32
6
76
67
39
62
22
33
43
14
10
87
37
30
31
73
86
40
81
90
The answer I am looking for in this example would be 87.

 

[CodeNinja]

=index(a1:a20,aggregate(15,6,row(a9:a20)/--(a9:a20>max(a1:a8)),1))

 

[seh27]

=index(a1:a20,aggregate(15,6,row(a9:a20)/--(a9:a20>max(a1:a8)),1))

Thanks! I'm going to try to see if that works. I actually have headings in the first row, so everything is shifted down one cell, which I can fix accordingly, but after the aggregate, do I have to change the numbers 15 and 6? Thanks.

 

[seh27]

Thanks! I'm going to try to see if that works. I actually have headings in the first row, so everything is shifted down one cell, which I can fix accordingly, but after the aggregate, do I have to change the numbers 15 and 6? Thanks.

I did try changing the numbers just to see what happens, sometimes I'll get an error and sometimes I'll get a number but even if I do get a number it is the incorrect answer.

 

[CodeNinja]

Dont change 15 and 6... 15 refers to small in the function and 6 refers to ignore errors... If you change the A1, A8, A9, and A20 the function should work properly.


Like this:
=INDEX(A2:A21,AGGREGATE(15,6,ROW(A10:A21)/--(A10:A21>MAX(A2:A9)),1))

 

[seh27]

I haven't had access to a computer the last few days. But I just tried again how you told me and it didn't work. Another thing I didn't mention was that this is also in the second column. I didn't think it would make a difference, I just changed everything from A to B but maybe it does make a difference? In either case, thank you so much for trying!

 

[CodeNinja]

Please post an accurate example of the data and the formula you are using... The formula I provided, if adjusted properly, will work.

 

[Rick Rothstein]

I think this array-entered** formula should work...

=INDEX(B1:B21,MIN(IF(B10:B21>MAX(B2:B9),ROW(B10:B21))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[seh27]

That one didn't work either. I really appreciate everyone trying. Here is a better example of what I am trying to do and a better explanation:
The applicant order column is there for a different formula in my sheet. I just put it in the example so you can see that the Applicatn Rank is in Column B. The Applicant Rank column is all a list of random numbers between 0 and 100. I am trying to prove an optimal stopping theory to the Applicant Rank column, which is, if the first 8 numbers are ignored, you will still end up with a higher rank than the maximum number of the first 8 numbers. So I want to find the first number after the first 8 numbers that is greater than the maximum of the first 8 numbers. If there is no number greater than the first 8, then the end result will just be the last number in the list. I know that the start of my formula will be:
IF(MAX(B2:B9) > MAX(B10:B21),B21, )
What I want to figure out is the else part of the If statement, which I already stated, how to get the first number greater than the max of the first 8 numbers. Another problem I have ran into is that because the numbers in Applicant Rank are all random numbers, some appear more than one time in the list. I am not sure how to fix that or what to do with the formula to accomodate that. Once again thanks for any responses!
[TABLE="width: 150"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Applicant Order[/TD]
[TD="align: center"]Applicant Rank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]13[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]15[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]16[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]17[/TD]
[TD]73[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]18[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]19[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]20[/TD]
[TD]81[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

That one didn't work either.

Another problem I have ran into is that because the numbers in Applicant Rank are all random numbers, some appear more than one time in the list. I am not sure how to fix that or what to do with the formula to accomodate that.

Other than not reporting the last number in the list when no number in B10:B21 is greater than the maximum in B2:B9 (which you did not mention in your original post), in what way did my formula not work? As far as I can tell, it works exactly like you asked for (assuming you entered into the formula bar like I instructed). Here it is again, with the additional requirement you have now asked for...

=IF(MAX(B2:B9)>=MAX(B10:B21),B21,INDEX(B1:B21,MIN(IF(B10:B21>MAX(B2:B9),ROW(B10:B21)))))

Note: This is an array-entered formula, so you must commit
      it using CTRL+SHIFT+ENTER, not just Enter by itself

As for the other problem you mentioned, I do not understand what the repeated numbers have to do with your original request, so I am not sure what kind of "fix" you would want... please clarify this for us.