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

[Nitehawkhp]

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 Name	Contents
Setup	Setup 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
Players	Team / Player Names
Annual Scores	Record of all games played by teams or players
High Scores	Record of highest scores
Champions	Record 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.
[TABLE="width: 996"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 8"]SCORES RECORDED BY YEAR[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Max Score[/TD]
[TD]# Players Max score[/TD]
[TD]Player[/TD]
[TD]Player[/TD]
[TD]Player[/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD]Player[/TD]
[TD]Game 1[/TD]
[TD]Game 2[/TD]
[TD]Game 3[/TD]
[TD]Game 4[/TD]
[TD]Game 5[/TD]
[TD]Tie[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25 Dec 16[/TD]
[TD]Player 13[/TD]
[TD]160[/TD]
[TD]230[/TD]
[TD]360[/TD]
[TD]430[/TD]
[TD]240[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]25 Dec 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]25 Dec 16[/TD]
[TD]Player 8[/TD]
[TD]170[/TD]
[TD]220[/TD]
[TD]370[/TD]
[TD]420[/TD]
[TD]230[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]25 Jan 17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]25 Jan 17[/TD]
[TD]Player 5[/TD]
[TD]180[/TD]
[TD]210[/TD]
[TD]380[/TD]
[TD]410[/TD]
[TD]220[/TD]
[TD]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]25 Jan 17[/TD]
[TD]Player 10[/TD]
[TD]190[/TD]
[TD]200[/TD]
[TD]390[/TD]
[TD]400[/TD]
[TD]210[/TD]
[TD]380[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]25 Dec 18[/TD]
[TD]Player 3[/TD]
[TD]24[/TD]
[TD]260[/TD]
[TD]160[/TD]
[TD]2[/TD]
[TD]140[/TD]
[TD]260[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]25 Dec 18[/TD]
[TD]Player 2[/TD]
[TD]14[/TD]
[TD]10[/TD]
[TD]170[/TD]
[TD]1[/TD]
[TD]150[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Fluff]

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]

All three formula

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SCORES RECORDED BY YEAR									Date	Max Score	# Players Max score	Player	Player	Player	Player
2	Date	Player	Game 1	Game 2	Game 3	Game 4	Game 5	Tie
3	25-Dec-16	Player 13	160	230	360	430	240	60		25-Dec-16	430	3	Player 13	Player 8	Player 5
4	25-Dec-16	Player 8	170	220	370	420	430	60
5	25-Dec-16	Player 5	180	430	380	410	220	160
6	25-Jan-17	Player 10	190	200	390	400	210	380
7	25-Dec-18	Player 3	24	260	160	2	140	260
8	25-Dec-18	Player 2	14	10	170	1	150	10
PriorDay

Cell Formulas
Range	Formula
L3	=SUMPRODUCT(($A$3:$A$8=$J3)*($C$3:$H$8=$K3))
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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nitehawkhp]

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]

You're welcome & thanks for the feedback

 

[Nitehawkhp]

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.

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Max Score[/TD]
[TD="align: center"]# Players Max Score[/TD]
[TD="align: center"]Player[/TD]
[TD="align: center"]Player[/TD]
[TD="align: center"]Player[/TD]
[TD="align: center"]Player[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]25-Dec-2016[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Player 8[/TD]
[TD="align: center"]Player 13[/TD]
[TD="align: center"]Player 16[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]25-Dec-2017[/TD]
[TD="align: center"]470[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Player 3[/TD]
[TD="align: center"]Player 8[/TD]
[TD="align: center"]Player 14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]25-Dec-2018[/TD]
[TD="align: center"]360[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Player 2[/TD]
[TD="align: center"]Player 5[/TD]
[TD="align: center"]Player 10[/TD]
[TD]Player 13[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px, align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]K3[/TH]
[TD]{=MAX(IF($A$3:$A$8=$J3,$C$3:$H$8))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]M3[/TH]
[TD]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]N3[/TH]
[TD]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]O3[/TH]
[TD]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]P3[/TH]
[TD]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

You're welcome & thanks for the feedback.

ExcelJet has quite a lot of useful formulae