Dealing with duplicates/normalizing values in a MAX VLOOKUP and INDEX

0

I'm trying to compose a VLOOKUP that will help me score bar trivia. My table is simple, a list of teams on the leftmost column, and then score columns throughout, i.e.

Team R1 
A    3
B    2
C    8
D    6
E    10

I have one lookup for the winning/highest score:

=VLOOKUP(MAX(B2:B13),B1:B13,1,0)

which works fairly well, and then an indexer to pull back the team name with the highest score at a glance:

=INDEX(A2:A13,MATCH(MAX(B2:B13),B2:B13,0))

which also works reasonably. For the sample set above, the VLOOKUP returns the max score of 10, and the indexer returns the team which won the round, E.

However, I have two problems for which I am wondering what the cleanest solution might be:

1) When there are ties, the INDEXER for winning team will only return the first instance of the highest score it finds. Ideally, I'd like to return all teams who have hit max values.

2) It's a quirk of the ruleset that you can double points for a round once, but cannot use doubled points to win a round. Since rounds are always scored out of 10, I'd like to halve any score higher than 10 for purposes of evaluating a winner.

Any help/pointers would be greatly appreciated.

user2395694

Posted 2019-10-25T17:38:48.970

Reputation: 115

No answers