Return largest values based on text criteria

[klimax]

racking my brain trying every various combinations of formula, but using the following general setup have the user provide a "name" and a "skill" and have formula return the three most recent dates and corresponding grades.

Biggest problem i have is that the raw data is in the format that the "skills" are bunched together in a single cell per-instance of date.

in this setup, if i was to provide the name "Karen" and the skill of "jumping" i would like it to return....
1(1/5/2018-84) 2(1/4/2018-85) 3(1/3/2018-62)

the more i look at it, the more it seems that i may need to have another set of columns to split up the text in the 'skill(s)' cell, but i am trying to avoid that.

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G	H
Name	Date	Grade	Skill(s)
Bob	Running,jumping,sports	Name	Karen
Karen	Running,jumping,sports	Skill	Jumping
Sally	Running,jumping,sports
Bob	Running,jumping,sports
Karen	Running,jumping,sports	Most recent grades based on skill
Sally	Running,sports	n-th	Date	Score
Bob	Running,jumping,sports
Karen	Running,jumping,sports
Sally	Running,jumping,sports
Bob	Running,jumping
Karen	Running,jumping
Sally	Running,jumping,sports
Bob	Running
Karen	Running,jumping
Sally	Running,Sports

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]70[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]76[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]79[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]78[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]76[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]62[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]84[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]62[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]85[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]67[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]62[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]83[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]85[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]62[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]94[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]84[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]70[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[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"]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] "]G8[/TH]
[TD="align: left"]{=LARGE(IF($A$2:$A$16=$G$2,IF(ISNUMBER(SEARCH($G$3,$D$2:$D$16)),$B$2:$B$16)),ROWS($G$8:$G8))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]{=INDEX($C$2:$C$16,SMALL(IF($A$2:$A$16=$G$2,IF($B$2:$B$16=G8,IF(ISNUMBER(SEARCH($G$3,$D$2:$D$16)),ROW($A$2:$A$16)-ROW($A$2)+1))),COUNTIF($G$8:$G8,G8)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




The formula in H8 could be a tad simpler, but I allowed for the possibility of multiple scores on the same date.