How to Search for and return the last 12 matches of a value in either of two columns of a table in one worksheet and return those rows in a second wor

[widgewilliams]

@widgewilliams

This thread is old and getting very big - difficult to locate the formula you are referring to and the corresponding data.

I suggest you create a new thread by showing us a small sample of your data along with expected results.

To post a sample of your data see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Then, if you wish, you can put a link here to the new thread.

M.

As requested I've started a new post for this question.

In one worksheet "Historical Data" I have a table A1:BV1825. The data in that table is 5 years worth or English Premier League Results and is constantly being added to. Columns B through J are the ones of interest for this question.

	B	C	D	E	F	G	H	I	J
2	Date	HomeTeam	AwayTeam	FTHG	FTAG	FTR	HTHG	HTAG	HTR
3	18/08/12	Arsenal	Sunderland	0	0	D	0	0	D
4	18/08/12	Fulham	Norwich	5	0	H	2	0	H
5	18/08/12	Newcastle	Tottenham	2	1	H	0	0	D
6	18/08/12	QPR	Swansea	0	5	A	0	1	A
7	18/08/12	Reading	Stoke	1	1	D	0	1	A
8	18/08/12	West Brom	Liverpool	3	0	H	1	0	H
9	18/08/12	West Ham	Aston Villa	1	0	H	1	0	H
10	19/08/12	Man City	Southampton	3	2	H	1	0	H

<tbody>
</tbody>

Each week new rows are added to the bottom of the table as the football seasons progress. Currently the last row is 1825.

1825

5/4/2017

Swansea

Tottenham

1

3

A

1

0

H

<tbody>
</tbody>

In a seperate worksheet "Output" I have a Drop Down List containing the names of each team in the English Premiership.

By selecting any particular team in the drop down list I am looking to pull a range of statistics for that team and display them in the Output worksheet.

One of those sets of statistics is the Current Form of the team over their last 12 games - both home and away.

What I (think) I need to be able to do is to look up columns C and D in the "Historical Data" worksheet to find matches to the drop down box Output!$B$2 and return the last 12 rows where there is a match to get both the Home and Away Form for the particular team I'm interested in and create a new table in the Output worksheet for those last 12 games with the following headings

Date

Home Team

Away Team

Half Time Score

Full Time Score

<tbody>
</tbody>

I know that the following formula would find and display what I am after...

{=IFERROR(INDEX('Historical Data'!B$2:B$9999,SMALL(IF((('Historical Data'!$C$2:$C$9999=Output!$B$2)+('Historical Data'!$D$2:$D$9999=Output!$B$2)),ROW('Historical Data'!B$2:B$9999)-ROW('Historical Data'!B$2)+1),ROWS('Historical Data'!B$2:'Historical Data'!B2))),"")}

However, it will find and match working from row 1 downwards and so extract the oldest data first as shown below (with West Brom being the search value in this instance)

Date	Home Team	Away Team	Half Time	Full Time
18/08/12	West Brom	Liverpool	1	0	H	3	0	H
25/08/12	Tottenham	West Brom	0	0	D	1	1	D
1/9/2012	West Brom	Everton	0	0	D	2	0	H
15/09/12	Fulham	West Brom	2	0	H	3	0	H
22/09/12	West Brom	Reading	0	0	D	1	0	H
30/09/12	Aston Villa	West Brom	0	0	D	1	1	D
6/10/2012	West Brom	QPR	2	1	H	3	2	H
20/10/12	West Brom	Man City	0	0	D	1	2	A
28/10/12	Newcastle	West Brom	1	0	H	2	1	H
5/11/2012	West Brom	Southampton	1	0	H	2	0	H
10/11/2012	Wigan	West Brom	1	2	A	1	2	A
17/11/12	West Brom	Chelsea	1	1	D	2	1	H

<colgroup><col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1536;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="11" width="40"> </colgroup><tbody>
</tbody>

What I am needing to do is start from the most recent matches which are to be found at the bottom of the historic data table and work backwards.

Can it be done, and how could I do it?

Thanks

 

[Eric W]

Try putting LARGE instead of SMALL in that formula.

 

[widgewilliams]

Try putting LARGE instead of SMALL in that formula.

Well, that was remarkably simple....

Date	Home Team	Away Team	Half Time	Full Time
4/4/2017	Watford	West Brom	1	0	H	2	0	H
4/1/2017	Man United	West Brom	0	0	D	0	0	D
3/18/2017	West Brom	Arsenal	1	1	D	3	1	H
3/11/2017	Everton	West Brom	2	0	H	3	0	H
3/4/2017	West Brom	Crystal Palace	0	0	D	0	2	A
2/25/2017	West Brom	Bournemouth	2	1	H	2	1	H
2/11/2017	West Ham	West Brom	0	1	A	2	2	D
2/4/2017	West Brom	Stoke	1	0	H	1	0	H
1/31/2017	Middlesbrough	West Brom	1	1	D	1	1	D
1/21/2017	West Brom	Sunderland	2	0	H	2	0	H
1/14/2017	Tottenham	West Brom	2	0	H	4	0	H
1/2/2017	West Brom	Hull	0	1	A	3	1	H

<colgroup><col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1536;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="11" width="40"> </colgroup><tbody>
</tbody>

A bit of cut and pasting to rearrange the order...

Date	Home Team	Away Team	Half Time	Full Time
1/2/2017	West Brom	Hull	0	1	A	3	1	H
1/14/2017	Tottenham	West Brom	2	0	H	4	0	H
1/21/2017	West Brom	Sunderland	2	0	H	2	0	H
1/31/2017	Middlesbrough	West Brom	1	1	D	1	1	D
2/4/2017	West Brom	Stoke	1	0	H	1	0	H
2/11/2017	West Ham	West Brom	0	1	A	2	2	D
2/25/2017	West Brom	Bournemouth	2	1	H	2	1	H
3/4/2017	West Brom	Crystal Palace	0	0	D	0	2	A
3/11/2017	Everton	West Brom	2	0	H	3	0	H
3/18/2017	West Brom	Arsenal	1	1	D	3	1	H
4/1/2017	Man United	West Brom	0	0	D	0	0	D
4/4/2017	Watford	West Brom	1	0	H	2	0	H

<colgroup><col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1536;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="11" width="40"> </colgroup><tbody>
</tbody>

And it is exactly what I was wanting to achieve. Clearly i need to understand what the Large and Small functions actually do.

Thank you so much Eric.

 

[DRSteele]

Don't be confused my my setup here - I used your 'output' data as my source data. I extracted the most recent game into Row 18, the second most recent into Row 19 and onwards. I used an expanding range as part of the function AGGREGATE, whose function_num is 14 for LARGE. Copy B18 rightways. Then Copy Row 18 downwards for as many games as you want to extract (up to the number of games the team played in the master list (12 in my case).

	A	B	C	D	E	F	G	H	I
1	Date	Home Team	Away Team	Half Time			Full Time
2	8/18/2012	West Brom	Liverpool	1	0	H	3	0	H
3	8/25/2012	Tottenham	West Brom	0	0	D	1	1	D
4	9/1/2012	West Brom	Everton	0	0	D	2	0	H
5	9/15/2012	Fulham	West Brom	2	0	H	3	0	H
6	9/22/2012	West Brom	Reading	0	0	D	1	0	H
7	9/30/2012	Aston Villa	West Brom	0	0	D	1	1	D
8	10/6/2012	West Brom	QPR	2	1	H	3	2	H
9	10/20/2012	West Brom	Man City	0	0	D	1	2	A
10	10/28/2012	Newcastle	West Brom	1	0	H	2	1	H
11	11/5/2012	West Brom	Southampton	1	0	H	2	0	H
12	11/10/2012	Wigan	West Brom	1	2	A	1	2	A
13	11/17/2012	West Brom	Chelsea	1	1	D	2	1	H
14
15	Lookup:	West Brom
16
17	Date	Home Team	Away Team	Half Time Score			Full Time Score
18	11/17/2012	West Brom	Chelsea	1	1	D	2	1	H
19	11/10/2012	Wigan	West Brom	1	2	A	1	2	A
20	11/5/2012	West Brom	Southampton	1	0	H	2	0	H
21	et cetera

<tbody>
</tbody>

Sheet58​

Worksheet Formulas Cell Formula A18 =AGGREGATE(14,6,($B$15=$B$2:$C$13)*$A$2:$A$13,ROWS($A$18:A18)) B18 =INDEX(B$2:B$13,MATCH($A18,$A$2:$A$13)) <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

To avoid the cutting and pasting, you could do this:

{=IFERROR(INDEX('Historical Data'!B$2:B$9999,LARGE(IF((('Historical Data'!$C$2:$C$9999=Output!$B$2)+('Historical Data'!$D$2:$D$9999=Output!$B$2)),ROW('Historical Data'!B$2:B$9999)-ROW('Historical Data'!B$2)+1),13-ROWS('Historical Data'!B$2:'Historical Data'!B2))),"")}

Glad we could help.

 

[widgewilliams]

To avoid the cutting and pasting, you could do this:

{=IFERROR(INDEX('Historical Data'!B$2:B$9999,LARGE(IF((('Historical Data'!$C$2:$C$9999=Output!$B$2)+('Historical Data'!$D$2:$D$9999=Output!$B$2)),ROW('Historical Data'!B$2:B$9999)-ROW('Historical Data'!B$2)+1),13-ROWS('Historical Data'!B$2:'Historical Data'!B2))),"")}

Glad we could help.

That certainly does the trick Eric. Thanks again.

Eric certainly seems to have provided a solution to my question and fixed the need for cutting and pasting, but having taken the time to respond to me DRSteele it would only be right for me to take a look at your proposed solution also which, i suspect, would help me in some of the other things I am wanting to do.

I've have encountered a problem though in that I get the following results.

4/4/2017	Watford	West Brom	1	0	H	2	0	H
4/1/2017	Watford	Sunderland	0	0	D	1	0	H
3/18/2017	West Ham	Leicester	1	3	A	2	3	A
3/11/2017	Hull	Swansea	0	0	D	2	1	H
3/4/2017	West Brom	Crystal Palace	0	0	D	0	2	A
2/25/2017	West Brom	Bournemouth	2	1	H	2	1	H
2/11/2017	West Ham	West Brom	0	1	A	2	2	D
2/4/2017	West Brom	Stoke	1	0	H	1	0	H
1/31/2017	Swansea	Southampton	1	0	H	2	1	H
1/21/2017	West Brom	Sunderland	2	0	H	2	0	H
1/14/2017	West Ham	Crystal Palace	0	0	D	3	0	H
1/2/2017	West Ham	Man United	0	0	D	0	2	A

<tbody>
</tbody>

I suspect that the reason is because my actual source data contains every game played in the EPL, and not just West Brom's.

Would I be right in thinking that A18 pulls the dates of West Broms last 12 games, and B12 then matches to that date? If so then what seems to be happening is that B12 is retrieving the last game in the source data for that particular date, as can be seen below.

3/11/2017	Bournemouth	West Ham	3	2	H	1	1	D
3/11/2017	Everton	West Brom	3	0	H	2	0	H
3/11/2017	Hull	Swansea	2	1	H	0	0	D
3/12/2017	Liverpool	Burnley	2	1	H	1	1	D
3/18/2017	Bournemouth	Swansea	2	0	H	1	0	H
3/18/2017	Crystal Palace	Watford	1	0	H	0	0	D
3/18/2017	Everton	Hull	4	0	H	1	0	H
3/18/2017	Stoke	Chelsea	1	2	A	1	1	D
3/18/2017	Sunderland	Burnley	0	0	D	0	0	D
3/18/2017	West Brom	Arsenal	3	1	H	1	1	D
3/18/2017	West Ham	Leicester	2	3	A	1	3	A
3/19/2017	Man City	Liverpool	1	1	D	0	0	D
3/19/2017	Middlesbrough	Man United	1	3	A	0	1	A
3/19/2017	Tottenham	Southampton	2	1	H	2	0	H
4/1/2017	Burnley	Tottenham	0	2	A	0	0	D
4/1/2017	Chelsea	Crystal Palace	1	2	A	1	2	A
4/1/2017	Hull	West Ham	2	1	H	0	1	A
4/1/2017	Leicester	Stoke	2	0	H	1	0	H
4/1/2017	Liverpool	Everton	3	1	H	2	1	H
4/1/2017	Man United	West Brom	0	0	D	0	0	D
4/1/2017	Southampton	Bournemouth	0	0	D	0	0	D
4/1/2017	Watford	Sunderland	1	0	H	0	0	D
4/2/2017	Arsenal	Man City	2	2	D	1	2	A
4/2/2017	Swansea	Middlesbrough	0	0	D	0	0	D
4/4/2017	Burnley	Stoke	1	0	H	0	0	D
4/4/2017	Leicester	Sunderland	2	0	H	0	0	D
4/4/2017	Man United	Everton	1	1	D	0	1	A
4/4/2017	Watford	West Brom	2	0	H	1	0	H
5/4/2017	Arsenal	West Ham	3	0	H	0	0	D
5/4/2017	Chelsea	Man City	2	1	H	2	1	H
5/4/2017	Hull	Middlesbrough	4	2	H	3	2	H
5/4/2017	Liverpool	Bournemouth	2	2	D	1	1	D
5/4/2017	Southampton	Crystal Palace	3	1	H	1	1	D
5/4/2017	Swansea	Tottenham	1	3	A	1	0	H

<tbody>
</tbody>