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]

The "Fixed" formula for the aggregate function (no array formula required) with the new requirement that the last one in the list be selected if none are greater is:

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

Note: Aggregate is only in excel 2010. If you have 2007 or earlier you will have to use an array formula. I do prefer aggregate because it is a faster calculation time, but if you are only using it one or two times in the spreadsheet, the array version works great.

 

[seh27]

For some reason my excel worksheet did not follow the formula. I entered it as an array and it wouldn't produce a number even close to the right answer. I created a new file and tried the formula on there and the majority of the time it works! But I tried it multiple times because I need to for this simulation, and I found that sometimes it won't recognize the first number in the list that is higer than the max, or it won't recognize that there is a number higher at all and give back the last number in the list. I don't know why it won't work but I just have been manually checking the column to see what the answer should be and it's not always right. But very close!! thank you

 

[CodeNinja]

For some reason my excel worksheet did not follow the formula. I entered it as an array and it wouldn't produce a number even close to the right answer. I created a new file and tried the formula on there and the majority of the time it works! But I tried it multiple times because I need to for this simulation, and I found that sometimes it won't recognize the first number in the list that is higer than the max, or it won't recognize that there is a number higher at all and give back the last number in the list. I don't know why it won't work but I just have been manually checking the column to see what the answer should be and it's not always right. But very close!! thank you

Rick's Array formula works perfectly for me...

 

[seh27]

CodeNinja, yours seems to be working! Probably once again because I tried it in a new excel sheet instead of my original one. And I have to repeat this formula 100 times, so this will really help. Thanks both of you for all your help!

 

[Rick Rothstein]

For some reason my excel worksheet did not follow the formula. I entered it as an array and it wouldn't produce a number even close to the right answer. I created a new file and tried the formula on there and the majority of the time it works! But I tried it multiple times because I need to for this simulation, and I found that sometimes it won't recognize the first number in the list that is higer than the max, or it won't recognize that there is a number higher at all and give back the last number in the list. I don't know why it won't work but I just have been manually checking the column to see what the answer should be and it's not always right. But very close!! thank you

Where does your data start... cell A1 or A2? Your posting in Message #9 clearly shows them starting in cell A2, so I designed my formula to start there as well; however, if you changed things and started your data in cell A1, the formula I posted will "miss" any maximum values placed in cell A1. If your data really does start in cell A2 (like you indicated in Message #9), then the formula I posted in Message #10 should work fine for you. If on the other hand, you actually start your data in cell A1, then here is the formula to use for that situation...

=IF(MAX(B1:B8)>=MAX(B9:B20),B20,INDEX(B1:B20,MIN(IF(B9:B20>MAX(B1:B8),ROW(B9:B20)))))

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

 

[seh27]

My actual data does start at A2, the only thing that is in A1 is a title

 

[Rick Rothstein]

My actual data does start at A2, the only thing that is in A1 is a title

Can you show us two examples of 20 numbers where the formula I posted is giving you the wrong answer... once for when "it won't recognize the first number in the list that is higer than the max" and a second time for when won't recognize that there is a number higher at all and give back the last number in the list". Then we can try to figure out what is wrong and correct for it.

 

[seh27]

Mistake---Let me find a better example

 

[seh27]

I do apologize. It is working now. I must have been thinking wrong when I was looking at it earlier. Thank you for all your help and your patience with me.