Lookup/ Large/IF function - Return 5 most recent dates matching criterior

[Jzfbkr]

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Round[/TD]
[TD]Home Team[/TD]
[TD]Home Score[/TD]
[TD]Away Score[/TD]
[TD]Away Team[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD]1[/TD]
[TD]TEAM A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM B[/TD]
[/TR]
[TR]
[TD]12/09/2013[/TD]
[TD]1[/TD]
[TD]TEAM C[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]TEAM D[/TD]
[/TR]
[TR]
[TD]20/09/2013[/TD]
[TD]2[/TD]
[TD]TEAM C[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM A[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD]3[/TD]
[TD]TEAM A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]TEAM D[/TD]
[/TR]
[TR]
[TD]05/10/2013[/TD]
[TD]4[/TD]
[TD]TEAM C[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]TEAM B[/TD]
[/TR]
[TR]
[TD]06/10/2013[/TD]
[TD]4[/TD]
[TD]TEAM D[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]TEAM A[/TD]
[/TR]
[TR]
[TD]15/10/2013[/TD]
[TD]5[/TD]
[TD]TEAM A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM E[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 100, align: right"]
<tbody>[TR]
[TD]TEAM A[/TD]
[/TR]
</tbody>[/TABLE]













I would like to return using the Lookup/Large/If functions.

The last 5 fixtures of the team in the input cell in the top right cell.
to return something like this.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]Round[/TD]
[TD]Home Team[/TD]
[TD]Home Score[/TD]
[TD]Away Score[/TD]
[TD]Away Team[/TD]
[/TR]
[TR]
[TD]15/10/2013[/TD]
[TD]5[/TD]
[TD]TEAM A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM E[/TD]
[/TR]
[TR]
[TD]06/10/2013[/TD]
[TD]4[/TD]
[TD]TEAM D[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]TEAM A[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD]3[/TD]
[TD]TEAM A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]TEAM D[/TD]
[/TR]
[TR]
[TD]20/09/2013[/TD]
[TD]2[/TD]
[TD]TEAM C[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM A[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD]1[/TD]
[TD]TEAM A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]TEAM B[/TD]
[/TR]
</tbody>[/TABLE]










Can anybody help me with this.

Thanks

 

[cyrilbrd]

Given in A1:

Excel 2010
	A	B	C	D	E	F
1	Date	Round	Home Team	Home Score	Away Score	Away Team
2	13-Sep-13	1	TEAM A	1	1	TEAM B
3	12-Sep-13	1	TEAM C	2	1	TEAM D
4	20-Sep-13	2	TEAM C	1	1	TEAM A
5	27-Sep-13	3	TEAM A	2	1	TEAM D
6	05-Oct-13	4	TEAM C	2	2	TEAM B
7	06-Oct-13	4	TEAM D	1	4	TEAM A
8	15-Oct-13	5	TEAM A	1	1	TEAM E
9
10	Date	Round	Home Team	Home Score	Away Score	Away Team
11	15-Oct-13	5	TEAM A	1	1	TEAM E
12	06-Oct-13	4	TEAM D	1	4	TEAM A
13	27-Sep-13	3	TEAM A	2	1	TEAM D
14	20-Sep-13	2	TEAM C	1	1	TEAM A
15	13-Sep-13	1	TEAM A	1	1	TEAM B
Jzfbkr

Formula in A11 is =MAX(IF((B11=$B$2:$B$8),$A$2:$A$8)) Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC. Copied down till needed.
Formula in C11 is =INDEX(C$2:C$8,MATCH($A11,$A$2:$A$8,0)) copied down and right till needed.