Excluding data from Index?

Sorry if I'm repeating a question. New to this forum. I'm not even sure if my title is correct! My problem:

I have two sets of data. I can get the top two from one group:

=INDEX(B27:G27,MATCH(MAX(B28:G28),B28:G28,0))
=INDEX(B27:G27,MATCH(LARGE(B28:G28,2),B28:G28,0))

And the top two from the other group:

=INDEX(H27:M27,MATCH(MAX(H28:M28),H28:M28,0))
=INDEX(H27:M27,MATCH(LARGE(H28:M28,2),H28:M28,0))

But, how would I go about getting the highest one not already chosen. I had tried this (=INDEX(B27:M27,MATCH(LARGE(B28:M28,5),B28:M28,0))) but it doesn't work.

Group1: 12.33, 12.47, 13.68, 14.25, 8.86
Group2:10, 6.48, 11.80, 8.15, 4.65, 13.1

The next number should be 12.47, but my attempt is bringing back 12.33. Any thoughts?

Why are you using index?

The choose function is just right for this.

Please Login or Register  to view this content.

will return 12.47

Oh apologies! I'm using index to return the name that corresponds to the data. So row 27 is the text I'm wanting to populate in my formula, row 28 contains the data I'm trying to sort from.

I've attached an example of what I'm trying to do. Hopefully that helps a little more. I have figured out the top two data entries from each group, and I'm trying to find the next highest overall from both groups.

Example.xls

So I picked the two highest from Group1 & Group2 (4 total). I'd now like to find the highest overall from all of the data that wasn't in the "two highest" group. Sorry that I'm not explaining clearly.

Originally Posted by Speshul

From the sample file, try

=INDEX(A5:L5,MAX(MATCH(LARGE(F6:L6,3),F6:L6,0),MATCH(LARGE(A6:E6,3),A6:E6,0)))

This returns the third largest from each group, and then pulls the LARGEST of those two (using the MAX function), and returns the Group name index.

Hope this helps

AHHH THIS IS PERFECT! I knew it was going to have to be some sort of nested formulas; I just couldn't figure out the right combo. Thanks SO MUCH and thanks for the quick responses!!!