Returning Multiple Team Names with the highest score in the event of a tie

[fischera9850]

Hello,

I'm a commissioner in a fantasy football league. In this league we need to track data that the ESPN app can't. One of those is weekly high scores. The team who scores the most points wins money, in the event of a tie they split that money. In my sheet I am currently using a combination of index, match, and max to return the name of the team who scored the most points. The only problem is that if there is a tie it only returns the name of the team that appears first.

My formula is as follows =index(A1:BQ1,match(max(14:14),14:14,0)). In week one you'll notice that two teams tied with a score of 86. Any suggestions as to how I can return both team names?

P.S. - This is my first attempt at a post so I can't include links. Apologies for that.

If there is anything helpful I can share please let me know!

Thanks in advance for the help!

[Limor_OP]

Hi
What version of MS are you using? 365?

[AliGW]

It says Google Sheets - I'm moving the thread to the correct forum section.

[fischera9850]

Apologies, I didn't realize that there was a separate thread for google sheets. I also realized that I could download a copy of my sheet an upload it here.

In the Week 1 tab, cell B19 you'll see the formula I'm currently using. It works well, but it doesn't work when there are ties. Which you'll see that "Team Kurz" is tied with "Team B". I'm looking for away to return both team names so I can reference them in my "Overview" tab and place the winnings in their column.

[janmorris]

My formula is as follows

the formula is WHERE?

In week one you'll notice that two teams tied with a score of 86.

theres a lot of data in your sample.. so again.. WHERE?

Any suggestions as to how I can return both team names

WHICH TEAMS ?

NOTE: when you want to explain what you can see, remember that you know your data, everyone else has to decipher it.. so help us to help you and be definite with your description.. it will save us time which then keeps interest

[fischera9850]

Sorry I tend to put more text than I need to.

I mentioned above that the formula I need to edit is in cell B19 in the "Week 1" tab. It currently does what is supposed to and returns "Team Kurz" but that team is tied with "Team B". And I want to somehow return both teams since they are tied.

[janmorris]

Here is formula that will get the expected results:

Please Login or Register  to view this content.

first we TRANSPOSE row 1 and 14
then we QUERY out all rows where Col1 (of the transposed data) is not null
then we SORTN to get the # 1 score, with ties mode #3 that anything matching the #1 score must be included, and we do this by sorting the scores (Col2) in descending
then we QUERY select the Col1 which is the team names

[fischera9850]

(Posting my follow up question in a new thread since my original question was resolved)