Return 4th highest number not in list.

[JO505]

From a given list of numbers I want to return the 4th highest number that is not in the list.

If a given list is 1, 2, 3 then the next highest number is 4 but if the list were 1, 6, 3 the 4th highest number not listed is 2. I'm not sure how to accomplish this. A list of 1, 2, 7 would return 3.

Jim O

[Jdevil]

I don't understand what you mean by "4th highest number". How is 2 the 4th highest number in 1,6,3? Don't you mean the lowest (not already there) integer? And do you always only have 3 numbers?

[JO505]

Sorry for the confusion (I'll blame it on Monday). Yes their are always 3 starting numbers.

I am trying to compile a list of sports teams (MLB) wild card teams. The example shows what I have so far but for the
sake of clarity I have not included the formulas that build this example.

I am looking to populate cells F16 & J16 with the next best record not listed in the cells F12:F14 & J12:J14.

Hope this helps.

Jim O

[cyiangou]

Yes, you can do something like the attached:

rank_unused_cy.xlsx

• Column B shows the list of possible numbers that can be selected
• Column E shows the list of numbers that HAVE been selected
• Column H shows the numbers that have NOT been selected
• Column I shows the unselected numbers ranked in descending order

If you want the 4th highest unused number, just VLOOKUP '4' in column G to get 80.

[Jdevil]

Here is a simple way:

=IF(AND(F12<>1,F13<>1,F14<>1),1,IF(AND(F12<>2,F13<>2,F14<>2),2,IF(AND(F12<>3,F13<>3,F14<>3),3,4)))

[JO505]

Jdevil,

That seems to be the answer.

Thanks very much for your input.

Jim O