Extract data from TABLE, based on DATE and find MAX score, #of players, and player's name.

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Hello,
First let me give you a little background on my problem. This is my first post in this forum. I have tried to find a solution to my problem by searching the web and several forums. However, I have not found one. I have been able to get some segments to work but need some assistance to finish my creation.

I have three table top Skeeball machines will accommodate two players on each game. The machines keep score for each round of two players, and each Christmas my family has a friendly competition playing Skeeball

I have created several sheets in Excel to generate the tournament brackets, track players, determine winners of each game, and announce the winner of the competition. (With macros to control each function, for adding players, moving data, and printing winner certificates. I want to add a sheet that will store the scores, players, and champion, of each year.

I have a workbook with the following configuration:

Worksheet NameContents
SetupSetup information
Team names / Player names
Seed information
8 Teams / Players
16 Teams / Players
Tournament Bracket
8 Team Score
16 Team Score
Scores by Team / Player for each round of play
First Round
Second Round
Quarter Final Round
Final Round
PlayersTeam / Player Names
Annual ScoresRecord of all games played by teams or players
High ScoresRecord of highest scores
ChampionsRecord of the champions for each year

<tbody>
</tbody>

You can see below that; columns A through H contains sample (totally fictitious) scores recorded by year.
ABCDEFGHIJKLMNOP
1SCORES RECORDED BY YEARDateMax Score# Players Max scorePlayerPlayerPlayerPlayer
2DatePlayerGame 1Game 2Game 3Game 4Game 5Tie
325 Dec 16Player 131602303604302406025 Dec 16
425 Dec 16Player 81702203704202306025 Jan 17
525 Jan 17Player 5180210380410220160
625 Jan 17Player 10190200390400210380
725 Dec 18Player 3242601602140260
825 Dec 18Player 21410170115010

<tbody>
</tbody>

What I want to do:
Based on the date, in column J, I want to extract the highest score (column K) and the player (column L). I would also like to know, if there are multiple players with the highest score. Perhaps placing additional players in columns M, N, O, and P.

In other words, I want to specify the data ranges as follows:
Date: $A$3:$A$5000
Scores: $C$3:$H$5000

At the end of our tournament, the only input from me, would be to enter the date 25 Dec 18 into cell J5, and the formula in K5 would tell me the max score for all games played on 25 Dec 2018. The formula in K5 will display the number of players that have that max score, and if there were more than one player to have the same max score, the player name would be displayed in N5, O5, and P5.

Formulas used:
K5 – =Max(if(J5=$A$3:$A$5000,Scores) – Doesn’t work returns max value in scores.
K5 – =INDEX(MAX($$A$3:$A$5000),MATCH(J3,$A$3:$A$5000)) =Doesn’t work returns #REF!
L5 – =COUNTIF(Date,J5) – This only counts the number of times that the date in column A matches the date in column J

Can someone please offer me some direction? I can’t find the ‘key’ to unlock this mystery.

Thanks,

Rod
Thanks,

Rod
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.
Did you confirm the Max(If formula with Ctrl Shift Enter, rather than just enter? If so it should be wrapped in {}
For L5 try
=SUMPRODUCT(($A$3:$A$8=$J3)*($C$3:$H$8=$K3))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows
All three formula


<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">SCORES RECORDED BY YEAR</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style=";">Date</td><td style=";">Max Score</td><td style=";"># Players Max score</td><td style=";">Player</td><td style=";">Player</td><td style=";">Player</td><td style=";">Player</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Date</td><td style=";">Player</td><td style=";">Game 1</td><td style=";">Game 2</td><td style=";">Game 3</td><td style=";">Game 4</td><td style=";">Game 5</td><td style=";">Tie</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">25-Dec-16</td><td style=";">Player 13</td><td style="text-align: right;;">160</td><td style="text-align: right;;">230</td><td style="text-align: right;;">360</td><td style="text-align: right;;">430</td><td style="text-align: right;;">240</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;">25-Dec-16</td><td style="text-align: right;;">430</td><td style="text-align: right;;">3</td><td style=";">Player 13</td><td style=";">Player 8</td><td style=";">Player 5</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">25-Dec-16</td><td style=";">Player 8</td><td style="text-align: right;;">170</td><td style="text-align: right;;">220</td><td style="text-align: right;;">370</td><td style="text-align: right;;">420</td><td style="text-align: right;;">430</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">25-Dec-16</td><td style=";">Player 5</td><td style="text-align: right;;">180</td><td style="text-align: right;;">430</td><td style="text-align: right;;">380</td><td style="text-align: right;;">410</td><td style="text-align: right;;">220</td><td style="text-align: right;;">160</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">25-Jan-17</td><td style=";">Player 10</td><td style="text-align: right;;">190</td><td style="text-align: right;;">200</td><td style="text-align: right;;">390</td><td style="text-align: right;;">400</td><td style="text-align: right;;">210</td><td style="text-align: right;;">380</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">25-Dec-18</td><td style=";">Player 3</td><td style="text-align: right;;">24</td><td style="text-align: right;;">260</td><td style="text-align: right;;">160</td><td style="text-align: right;;">2</td><td style="text-align: right;;">140</td><td style="text-align: right;;">260</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">25-Dec-18</td><td style=";">Player 2</td><td style="text-align: right;;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;">170</td><td style="text-align: right;;">1</td><td style="text-align: right;;">150</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">PriorDay</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$3:$A$8=$J3</font>)*(<font color="Red">$C$3:$H$8=$K3</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K3</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$3:$A$8=$J3,$C$3:$H$8</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$3:$B$8,SMALL(<font color="Green">IF(<font color="Purple">$C$3:$H$8=$K3,ROW(<font color="Teal">$A$3:$A$8</font>)-ROW(<font color="Teal">$A$3</font>)+1</font>),COLUMNS(<font color="Purple">$A:A</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$3:$B$8,SMALL(<font color="Green">IF(<font color="Purple">$C$3:$H$8=$K3,ROW(<font color="Teal">$A$3:$A$8</font>)-ROW(<font color="Teal">$A$3</font>)+1</font>),COLUMNS(<font color="Purple">$A:B</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$3:$B$8,SMALL(<font color="Green">IF(<font color="Purple">$C$3:$H$8=$K3,ROW(<font color="Teal">$A$3:$A$8</font>)-ROW(<font color="Teal">$A$3</font>)+1</font>),COLUMNS(<font color="Purple">$A:C</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$3:$B$8,SMALL(<font color="Green">IF(<font color="Purple">$C$3:$H$8=$K3,ROW(<font color="Teal">$A$3:$A$8</font>)-ROW(<font color="Teal">$A$3</font>)+1</font>),COLUMNS(<font color="Purple">$A:D</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Fluff,

I apologize for replying so late. I have some health issues and don’t always have the ability to do the things I would like to accomplish.

However, I want to thank you for answering my questions. Your solutions were spot on!! They work perfectly. I don’t know how much time it took you to author the formulas, but I had spent more than 20 hours researching and trying to author solutions.

Thank you again!

Nitehawkhp
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Fluff,

I have experienced a small problem with the formulas when I tried to extract the high scores for the following year, for example:

The columns J, K, L, M, N, O, P is where the issue is.

JKLMNOP
1DateMax Score# Players Max ScorePlayerPlayerPlayerPlayer
225-Dec-20165003Player 8Player 13Player 16
325-Dec-20174701Player 3Player 8Player 14
425-Dec-20183601Player 2Player 5Player 10Player 13

<tbody>
</tbody>


CellFormula
K3{=MAX(IF($A$3:$A$8=$J3,$C$3:$H$8))}
M3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:A))),"")}
N3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:B))),"")}
O3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:C))),"")}
P3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:D))),"")}

<thead>
</thead><tbody>
</tbody>

The formulas in row 2 work perfectly. The're no issue with those formulas.

The issues result when the formulas are copied to all rows below row 2.
The database that contains the dates, scores, and players contain the following information:

Data Player Game 1 Game 2 Game 3 Game 4 Game 5 Tie
25-Dec-2016 - Player 3 - 120 - 480 - 420 - 410 - 470 - 60
25-Dec-2016 - Player 8 - 470 - 380 - 490 - 500 - 370 - 60
25-Dec-2017 - Player 14 - 90 - 360 - 470 - 440 - 350 - 80

When the formulas in K3 and L3, extract the data from the database, they correctly identify (470) as Max score and one player (Player 14) for 25-Dec-2017.
When the formulas in M3, N3, and O3 extract the data from the database for 25-Dec-2017, they are finding the players (Player 3) and (Player 8) with 470 on 25-Dec-2016.

Thank you for any help you can offer to me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
=IFERROR(INDEX($B$3:$B$8,SMALL(IF(($C$3:$H$8=$K3)*($A$3:$A$8=$J3),ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:A))),"")
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Fluff,

That fixed it. Works just like I needed it to. I'm very appreciative for your help.
Do you have any recommendation on where I can learn more about array, large, and small formulas?

Thank you!

Nitehawkhp
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

ExcelJet has quite a lot of useful formulae
 

Watch MrExcel Video

Forum statistics

Threads
1,127,820
Messages
5,627,087
Members
416,219
Latest member
TommyBoy79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top