Last 5 Results

[jayyemm]

I've done some digging around and found some examples of what I'm looking for and tried to incorporate but not exactly working so I'm just looking for some more specific help. I am looking to determine the last 10 results of head to head matchups and also last 10 results overall. As you can see in the screenshot, the first 3 columns contains each player and D contains the results. Games/results are continuously added to the bottom. The "x" indicates who faced off in each game. What I'm finding confusing is finding each pIayer's last 5 results considering they might not have played the most recent game. I've tried using the following formula which contains the nested if and criteria but the formula just returns a value of 0 for Player A.

=COUNTIF(INDEX(D$2:D$1000,LARGE(IF(AND(D$2:D$1000="Player A", A$2:A$1000="<>"), ROW(D$2:E$1000)-ROW(D$2)+1),5)):D$1000,"Player A")

I would like to display it as a win-loss style similar to the records. I've been stuck here so I haven't had the opportunity to attempt at an overall last 5.

Thanks in advance for your time and help.

 

[Eric W]

Welcome to the Board!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Player A	Player B	Player C	Result			H2H		L5	Row of 5th last match
2	x		x	A			A vs. B	3-3	3-2	5
3	x	x		B			A vs. C	4-4	2-3	12
4		x	x	B			B vs. C	4-3	2-3	9
5	x	x		A
6	x	x		B
7	x		x	A
8		x	x	B
9		x	x	C
10	x		x	C
11	x	x		A
12	x		x	A
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
Sheet3

Cell Formulas
Range	Formula
H2	H2	=COUNTIFS(A:A,"x",B:B,"x",D:D,"A")&"-"&COUNTIFS(A:A,"x",B:B,"x",D:D,"B")
I2	I2	=COUNTIFS(INDEX(A:A,J2):A1000,"x",INDEX(B:B,J2):B1000,"x",INDEX(D:D,J2):D1000,"A")&"-"&COUNTIFS(INDEX(A:A,J2):A1000,"x",INDEX(B:B,J2):B1000,"x",INDEX(D:D,J2):D1000,"B")
J2	J2	=AGGREGATE(14,6,ROW($D$1:$D$1000)/($A$1:$A$1000="x")/($B$1:$B$1000="x"),5)
H3	H3	=COUNTIFS(A:A,"x",C:C,"x",D:D,"A")&"-"&COUNTIFS(A:A,"x",C:C,"x",D:D,"C")
I3	I3	=COUNTIFS(INDEX(A:A,J3):A1000,"x",INDEX(C:C,J3):C1000,"x",INDEX(D:D,J3):D1000,"A")&"-"&COUNTIFS(INDEX(A:A,J3):A1000,"x",INDEX(C:C,J3):C1000,"x",INDEX(D:D,J3):D1000,"C")
J3	J3	=AGGREGATE(14,6,ROW($D$1:$D$1000)/($A$1:$A$1000="x")/($C$1:$C$1000="x"),5)
H4	H4	=COUNTIFS(B:B,"x",C:C,"x",D:D,"B")&"-"&COUNTIFS(B:B,"x",C:C,"x",D:D,"C")
I4	I4	=COUNTIFS(INDEX(B:B,J4):B1000,"x",INDEX(C:C,J4):C1000,"x",INDEX(D:D,J4):D1000,"B")&"-"&COUNTIFS(INDEX(B:B,J4):B1000,"x",INDEX(C:C,J4):C1000,"x",INDEX(D:D,J4):D1000,"C")
J4	J4	=AGGREGATE(14,6,ROW($D$1:$D$1000)/($B$1:$B$1000="x")/($C$1:$C$1000="x"),5)

Note that column J is a helper column. I can incorporate it into the formula in column I, but it would make it quite long. You can hide column J if you want. Also, using LEFT, RIGHT, FIND, etc. I can get the player names from column G, so we only need a single formula in H you can drag down, and the same in I. But again, it would make them longer. Also, you can make the I formula into a "Last 10" matches formula just by changing the 5 in the J formula. Anyway, see if this does what you want.

 

[jayyemm]

Thank you for the welcome Eric! I am typically a lurker however I found myself getting very frustrated with this so decided to sign up and give it a shot.

The helper column is not a problem, I am okay with hiding it but I probably should have mentioned that I am primarily using Google Sheets. I didn't think there was much difference for what I was using it for....until now. The AGGREGATE function is not recognized in Google Sheets. From what I see, this will work exactly how I would like it to. I tried doing some research on how to duplicate the AGGREGATE function in Google Sheets however, not having much luck.

Something else I would like to add is the overall last 5 results as well. Would this be done the same way? Player A's last 5 results overall may be much different compared to specifically versus Player B.

 

[Eric W]

You really should have mentioned that you are using Google Sheets. While some things are the same, some are vastly different. I could have tried a different approach, or someone with more knowledge of Google Sheets could have stepped in.

I rewrote the formulas without AGGREGATE. This requires array formulas. In Excel you specify an array formula by pressing Control+Shift+Enter, in Google Sheets you surround the formula with =ARRAYFORMULA(...). And that's about all I know about Google Sheets.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	Result			H2H	H2H	L5		Name1	Name2	Row of 5th last match
2	x		x	A			A vs. B	3-3	3-2		A	B	5
3	x	x		B			A vs. C	4-4	2-3		A	C	12
4		x	x	B			B vs. C	4-3	2-3		B	C	9
5	x	x		A
6	x	x		B
7	x		x	A
8		x	x	B		Overall	A	B	C
9		x	x	C			7-7	7-6	7-8
10	x		x	C
11	x	x		A		Last 5 row	15	15	18
12	x		x	A		Last 5 record	2-3	2-3	3-2
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
23
Sheet3

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(MMULT((A$2:C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$25="x")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$25=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$25="x")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$25=L2))
I2:I4	I2	=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$25=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$25="x")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$25=L2))
K2:K4	K2	=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4	L2	=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4	M2	=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="x")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="x"),ROW(A$1:A$1000)),5)
G9:I9	G9	=COUNTIFS(INDEX($A:$C,0,MATCH(G8,$A$1:$C$1,0)),"x",$D:$D,G8)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G8,$A$1:$C$1,0)),"x",$D:$D,"<>"&G8)
G11:I11	G11	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G8,$A$1:$C$1,0))="x"),ROW(A$1:A$1000)),5)
G12:I12	G12	=COUNTIFS(INDEX($A:$C,G11,MATCH(G8,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G8,$A$1:$C$1,0)),"x",INDEX($D:$D,G11):$D$1000,G8)&"-"&COUNTIFS(INDEX($A:$C,G11,MATCH(G8,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G8,$A$1:$C$1,0)),"x",INDEX($D:$D,G11):$D$1000,"<>"&G8)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This has all the functions you asked for, using helper cells where appropriate. I hope it works for you, because I can't help any further. Good luck!

 

[jayyemm]

You are the man Eric! Works perfectly! Again, I am sorry for not mentioning that I am using Google Sheets. You should give yourself some more Google Sheets credit!

One last issue, I hope. What if the "x" was random text and not the same in each? They could be colors, shapes, rock, paper, scissors, etc while still not including blanks. Whatever replaces the "x" I want it to be able to still pull the same head to head data and last results. I tried replacing "x" in the formula's criteria with "<>" however that did not work.

 

[Eric W]

In the G12 COUNTIFS formula, you should be able to replace the "x" with "<>" as you suggested. In G9 formula, if you do the same thing, since I'm using whole column references, it'll count an extra loss for each player since it'll count the header row. Easiest way is to use "<>" and just put -1 on the end of the formula. Otherwise you'll need to change the references from whole column to $A$2:$A$1000 or whatever. For the other formulas, change ="x" to <>"" everywhere you see it.

 

[lrobbo314]

I know you're on Google Sheets, but if you come over to Excel from the dark side then you can use cool things like Power Query to do this kind of thing.

Book1
	A	B	C	D	E	F	G	H
1	Player A	Player B	Player C	Result		Matchup	H2H	Last5
2	x		x	A		AB	3-3	3-2
3	x	x		B		AC	4-4	2-3
4		x	x	B		BC	4-3	2-3
5	x	x		A
6	x	x		B
7	x		x	A
8		x	x	B
9		x	x	C
10	x		x	C
11	x	x		A
12	x		x	A
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Match = Table.AddColumn(Source, "Matchup", each if [Player A] = null then "BC" else if [Player B] = null then "AC" else "AB"),
    P1 = Table.AddColumn(Match, "P1", each if Text.Middle([Matchup],0,1) = [Result] then 1 else 0),
    P2 = Table.AddColumn(P1, "P2", each if [P1] = 1 then 0 else 1),
    Group = Table.Group(P2, {"Matchup"}, {{"P1", each List.Sum([P1]), type number}, {"P2", each List.Sum([P2]), type number}, {"Rows", each _, type table}}),
    Sort = Table.Sort(Group,{{"Matchup", Order.Ascending}}),
    H2H = Table.CombineColumns(Table.TransformColumnTypes(Sort, {{"P1", type text}, {"P2", type text}}, "en-US"),{"P1", "P2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"H2H"),
    Filter = Table.TransformColumns(H2H, {{"Rows", each Table.CombineColumns(Table.TransformColumnTypes(Table.Group(Table.LastN(Table.SelectRows(_, each [Matchup]=[Matchup]),5),{"Matchup"},{{"P1", each List.Sum([P1]), type number}, {"P2", each List.Sum([P2]), type number}, {"Rows", each _, type table}}),{{"P1", type text}, {"P2", type text}}, "en-US"),{"P1", "P2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Last5")}}),
    Last5 = Table.ExpandTableColumn(Filter, "Rows", {"Last5"}, {"Last5"})
in
    Last5

 

[jayyemm]

Eric - I surprisingly was able to figure out the "-1" in the G9 formulas when I first saw your response. I am still having trouble with replacing "x" though. I've replaced all "x" with "<>" and get an error immediately almost everywhere except the overall record. I also tried rearranging the overall last 5 to vertical, to represent more of a 1st, 2nd 3rd standings look. Obviously I have run into errors there and not sure where to correct that. I downloaded the file on Excel and shared via XL2BB this time around. You can see in G7, G8 and G9 I've used a find 1st, 2nd, 3rd most common so that as records change/improve this will be done automatically.

lrobbo - That seems a lot more simple from the amount of text involved lol but I'm sure more complex than what it seems. I mainly use Google Sheets for the ease of sharing and working with others. I'm sure Microsoft has something similar but Google just makes it easier for everyone.

Matchups - Standings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	Result			H2H		L5		Name 1	Name 2	Row of 5th Last Match
2	x		x	A			A vs. B	0-0	#NUM!		A	B	#NUM!
3	x	x		B			A vs. C	0-0	#NUM!		A	C	#NUM!
4		x	x	B			B vs. C	0-0	#NUM!		B	C	#NUM!
5	x	x		A
6	x	x		B			Overall		L5		Last 5 Row
7	x		x	A			B	13-11	#REF!		#REF!
8		x	x	B			C	13-13	#REF!		#REF!
9		x	x	C			A	11-16	#REF!		#REF!
10	x		x	C
11	x	x		A
12	x		x	A
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
23	x		x	A
24	x	x		B
25		x	x	B
26	x	x		A
27	x	x		A
28	x		x	C
29		x	x	C
30		x	x	B
31	x		x	C
32	x	x		B
33	x		x	C
34		x	x	B
35	x		x	C
36	x	x		B
37	x	x		A
38	x		x	C
Sheet4

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4	I2	=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
H3:H4	H3	=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K3),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L3),{1;1;1}),--(D$2:D$250=K3))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K3),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L3),{1;1;1}),--(D$2:D$250=L3))
K2:K4	K2	=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4	L2	=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4	M2	=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="<>")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="<>"),ROW(A$1:A$1000)),5)
G7	G7	=INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))
H7:H9	H7	=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9	I7	=COUNTIFS(INDEX($A:$C,K7,MATCH(#REF!,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(#REF!,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,#REF!)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(#REF!,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(#REF!,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&#REF!)
G8	G8	=INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0))))
G9	G9	=INDEX(D1:D1001,MODE(IF(((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0)))))*(D1:D1001<>INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0)))))),MATCH(D1:D1001,D1:D1001,0))))
K7	K7	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(A$1:A$1000)),5)
K8	K8	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(B$1:B$1000)),5)
K9	K9	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(#REF!,$A$1:$C$1,0))="<>"),ROW(C$1:C$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jayyemm]

I was able to fix the rearranging, it was just a referencing thing and everything worked but as soon as I replace all the "x" with "<>" I get the errors.

Matchup - Standings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	Result			H2H		L5		Name1	Name2	Row of 5th last match
2	x		x	A			A vs. B	0-0	#NUM!		A	B	#NUM!
3	x	x		B			A vs. C	0-0	#NUM!		A	C	#NUM!
4		x	x	B			B vs. C	0-0	#NUM!		B	C	#NUM!
5	x	x		A
6	x	x		B			Overall		L5		Last 5 Row
7	x		x	A			B	13-11	#NUM!		#NUM!
8		x	x	B			C	13-13	#NUM!		#NUM!
9		x	x	C			A	11-16	#NUM!		#NUM!
10	x		x	C
11	x	x		A
12	x		x	A
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
23	x		x	A
24	x	x		B
25		x	x	B
26	x	x		A
27	x	x		A
28	x		x	C
29		x	x	C
30		x	x	B
31	x		x	C
32	x	x		B
33	x		x	C
34		x	x	B
35	x		x	C
36	x	x		B
37	x	x		A
38	x		x	C
Sheet4

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4	I2	=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250="<>")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
K2:K4	K2	=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4	L2	=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4	M2	=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))="<>")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))="<>"),ROW(A$1:A$1000)),5)
G7	G7	=INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))
H7:H9	H7	=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9	I7	=COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,G7)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&G7)
G8	G8	=INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0))))
G9	G9	=INDEX(D1:D1001,MODE(IF(((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0)))))*(D1:D1001<>INDEX(D1:D1001,MODE(IF((D1:D1001<>"")*(D1:D1001<>INDEX(D1:D1001,MODE(IF(D1:D1001<>"",MATCH(D1:D1001,D1:D1001,0))))),MATCH(D1:D1001,D1:D1001,0)))))),MATCH(D1:D1001,D1:D1001,0))))
K7	K7	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G7,$A$1:$C$1,0))="<>"),ROW(A$1:A$1000)),5)
K8	K8	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G8,$A$1:$C$1,0))="<>"),ROW(B$1:B$1000)),5)
K9	K9	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G9,$A$1:$C$1,0))="<>"),ROW(C$1:C$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

I'm not sure where you got your G7:G9 formulas, so I just put the values from your sheet into those cells. Then for the rest of the formulas, I showed how to replace the ="x" with <>"".

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	Result			H2H		L5		Name1	Name2	Row of 5th last match
2	x		x	A			A vs. B	6-6	3-2		A	B	26
3	x	x		B			A vs. C	5-9	0-5		A	C	28
4		x	x	B			B vs. C	7-4	3-2		B	C	22
5	x	x		A
6	x	x		B			Overall		L5		Last 5 Row
7	x		x	A			B	13-11	4-1		30
8		x	x	B			C	13-13	4-1		31
9		x	x	C			A	11-16	1-4		33
10	x		x	C
11	x	x		A
12	x		x	A
13		x	x	B
14	x		x	C
15	x	x		A
16	x	x		B
17	x		x	C
18		x	x	B
19		x	x	C
20	x		x	A
21	x		x	C
22		x	x	C
23	x		x	A
24	x	x		B
25		x	x	B
26	x	x		A
27	x	x		A
28	x		x	C
29		x	x	C
30		x	x	B
31	x		x	C
32	x	x		B
33	x		x	C
34		x	x	B
35	x		x	C
36	x	x		B
37	x	x		A
38	x		x	C
39
Sheet8

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(MMULT((A$2:C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=K2))&"-"&SUMPRODUCT(MMULT((A$2:C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((A$2:C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(D$2:D$250=L2))
I2:I4	I2	=SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=K2))&"-"&SUMPRODUCT(MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=K2),{1;1;1}),MMULT((INDEX(A:A,M2):C$250<>"")*(A$1:C$1=L2),{1;1;1}),--(INDEX(D:D,M2):D$250=L2))
K2:K4	K2	=TRIM(LEFT(G2,FIND("vs.",G2)-1))
L2:L4	L2	=TRIM(MID(G2,FIND("vs.",G2)+3,99))
M2:M4	M2	=LARGE(IF((INDEX(A$1:C$1000,0,MATCH(K2,A$1:C$1,0))<>"")*(INDEX(A$1:C$1000,0,MATCH(L2,A$1:C$1,0))<>""),ROW(A$1:A$1000)),5)
H7:H9	H7	=COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,G7)&"-"&COUNTIFS(INDEX($A:$C,0,MATCH(G7,$A$1:$C$1,0)),"<>",$D:$D,"<>"&G7)
I7:I9	I7	=COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,G7)&"-"&COUNTIFS(INDEX($A:$C,K7,MATCH(G7,$A$1:$C$1,0)):INDEX($A:$C,1000,MATCH(G7,$A$1:$C$1,0)),"<>",INDEX($D:$D,K7):$D$1000,"<>"&G7)
K7:K9	K7	=LARGE(IF((INDEX($A$1:$C$1000,0,MATCH(G7,$A$1:$C$1,0))<>""),ROW(A$1:A$1000)),5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.