LAST 3 Events in 2 Seperate Columns?

[AYouQueTai]

Hello,
I have the NHL hockey games listed in my Schedule sheet like so.

Game ID | Status | Date | Visitor | Home | Vis Goals | Home Goals |
02001 | Completed | Jan18 | Montreal | Vancouver | 3 | 5
02002 | Completed | Jan18 | Toronto | Winnipeg | 4 | 2
02003 | Completed | Jan19 | Ottawa | Montreal | 5 | 1
02004 | Completed | Jan19 | Ottawa | Toronto | 6 | 2
02005 | Completed | Jan 20 | Montreal | Toronto | 3 | 2
02006 | Scheduled | Jan21 | Vancouver| Montreal | |

I would like to make a team sheet that lists a team's Last 3 "Completed" game Results.
Whether they were Visitor or Home doesn't matter
So in the Montreal Team Sheet it would display

Visitor | Home | Vis Goals | Home Goals |
Montreal | Vancouver | 3 | 5
Ottawa | Montreal | 5 | 1
Montreal | Toronto | 3 | 2

I can't figure this one out, can any of you?
Thanks,

 

[jasonb75]

@alz, would you be so kind as to run a timer test on this version as well please

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals		Montreal	Completed	(last 3 match)
2	2001	Completed	Jan18	Montreal	Vancouver	3	5		Visitor	Home	Vis Goals	Home Goals
3	2002	Completed	Jan18	toranto	Winnipeg	4	2		Montreal	Vancouver	3	5
4	2003	Completed	Jan19	Ottawa	Montreal	5	1		Ottawa	Montreal	5	1
5	2004	Completed	Jan19	Ottawa	Toronto	6	2		Montreal	Toronto	3	2
6	2005	Completed	Jan 20	Montreal	Toronto	3	2
7	2006	Scheduled	Jan21	Vancouver	Montreal
Sheet2

Cell Formulas
Range	Formula
I3:L5	I3	=IFERROR(INDEX(D$2:D$100000,AGGREGATE(14,6,ROW(D$2:D$100000)/($D$2:$E$100000=$I$1)/($B$2:$B$100000="Completed"),ROWS(I3:I$5))-ROW(D$2)+1),"")

 

[alz]

@jasonb75 I tested on both 100,000 rows and 500,000 rows .here is the result.

Excel Message board - Copy.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4		Montreal	Completed		ALZ
5		Visitor	Home	Vis Goals	Home Goals		Tested on 500,000 rows			Average	Rank				Tested on 100,000 rows			Average	Rank
6		Montreal	Toronto	3	2		2.45898	2.56859	2.48866	2.50541	4	34%		A	0.54634	0.53422	0.53229	0.537616667	4	25%
7		Montreal	Vancouver	3	5		A							B	0.74378	0.73362	0.73662	0.738006667	5	72%
8		Ottawa	Montreal	5	1									C	0.43547	0.43492	0.43254	0.43431	2	1%
9														D	0.43054	0.42673	0.42884	0.428703333	1	0%
10		Montreal	3	Fluff	original									E	0.49486	0.48639	0.49582	0.492356667	3	15%
11		Montreal	Toronto	3	2		3.45407	3.28822	3.19831	3.313533	5	77%
12		Montreal	Vancouver	3	5		B
13		Ottawa	Montreal	5	1
14
15		Montreal		Jason
16	3	Montreal	Toronto	3	2		1.8784	1.86305	1.86505	1.868833	1	0%
17	2	Montreal	Vancouver	3	5		C
18	1	Ottawa	Montreal	5	1
19
20		Montreal		Fluff	tweak
21		Montreal	Toronto	3	2		1.89972	1.92241	1.90522	1.909117	2	2%
22		Montreal	Vancouver	3	5		D
23		Ottawa	Montreal	5	1
24
25		Montreal		Jason
26		Montreal	Toronto	3	2		2.00512	1.9855	1.97612	1.988913	3	6%
27		Montreal	Vancouver	3	5		E
28		Ottawa	Montreal	5	1
30 (2)

Cell Formulas
Range	Formula
I6:L8	I6	=INDEX(D$2:D$500000,AGGREGATE(14,6,(ROW($D$2:$D$500000)-ROW($D$2)+1)/((--($B$2:$B$500000=$J$4))+(--($I$4=$D$2:$D$500000))+(--($I$4=$E$2:$E$500000))=2),ROWS($I6:$I$8)))
Q6,Q26,Q21,Q16,Q11	Q6	=AVERAGE(N6:P6)
R6,R26,R21,R16,R11	R6	=RANK(Q6,$Q$6:$Q$26,1)
S6,S26,S21,S16,S11	S6	=(Q6-$Q$16)/$Q$16
J10	J10	=MIN(3,COUNTIFS(D2:E7,I10,F2:G7,"<>"))
AA6:AA10	AA6	=(Y6-$Y$9)/$Y$9
I11:L13	I11	=IFERROR(INDEX(D$2:D$500000,AGGREGATE(14,6,(ROW($D$2:$D$500000)-ROW($D$2)+1)/(($D$2:$D$500000=$I$10)+($E$2:$E$500000=$I$10))/($B$2:$D$500000="Completed"),ROWS($I11:$I$13))),"")
I16:L18	I16	=INDEX(D$2:D$500000,AGGREGATE(14,6,ROW($D$2:$D$500000)/(($B$2:$B$500000="Completed")*(($I$15=$D$2:$D$500000)+($I$15=$E$2:$E$500000))>0),$H16)-ROW($D$2)+1)
I21:L23	I21	=IFERROR(INDEX(D$2:D$500000,AGGREGATE(14,6,(ROW($D$2:$D$500000)-ROW($D$2)+1)/(($D$2:$D$500000=$I$20)+($E$2:$E$500000=$I$20))/($B$2:$B$500000="Completed"),ROWS($I21:$I$23))),"")
I26:L28	I26	=IFERROR(INDEX(D$2:D$500000,AGGREGATE(14,6,ROW(D$2:D$500000)/($D$2:$E$500000=$I$25)/($B$2:$B$500000="Completed"),ROWS(I3:I$5))-ROW(D$2)+1),"")

 

[jasonb75]

Thanks for that @alz, I was curious to see how it compared. From your test results it appears that processing the 2 columns with the team names individually is more efficient than doing them together.

I didn't think that it would be an improvement but I was expecting it to be very close to the timing of the other formula, I was surprised by how much slower it was.