Extract a List of Values Filtered by Criteria with Sub-Arrays Tutorial Help

[CobraWave]

Hi all.

I'm looking to create a formula which will let me pick out rows which contain certain parameters (in various columns).

This tutorial proceeds to explain this, but only allows for one search parameter; I'd like multiple parameters.

Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel

Using the template given, I've modified the code to include an AND statement nested in the IF, theoretically solving my issue. But it doesn't work. All that is output is the very first row of the database, whether it fulfills the requirements or not.

My data structure is identical to the one given in the tutorial.

Here is my modified formula:

=IFERROR(
    INDEX('Car Data'!B$2:B$1156,
        SMALL(
            IF(
                AND(
                    'Car Data'!$H$2:$H$1156>='Filtered List'!$A$2,
                    'Car Data'!$E$2:$E$1156='Filtered List'!$B$2
                ),
                ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1
            ),
            ROWS('Car Data'!B$2:'Car Data'!B2)
        )
    )
,"")

The value for 'Filtered List'!$A$2 is set as 35, and the value for 'Filtered List'!$B$2 is set as 3.

Working with the template given, this should search for all cars which have greater than or equal to 35mpg and exactly 3 cylinders. But like I said there's an error somewhere.

And yes, I have dragged down the selection so the array is able to output completely, but all is output is Car Data Row 2.

Thanks.

 

[widgewilliams]

@widgewilliams

In which sheet are you implementing the formula and in which cell?

The formula will be implemented in the Output! sheet. I havent decided on the actual cell yet.

Basically, though. Historical Data comprises the following Columns. (There are actually more, but these are the ones Im currently concerned with)

B C D E F G H I J

Date	HomeTeam	AwayTeam	FTHG	FTAG	FTR	HTHG	HTAG	HTR
18/08/12	Arsenal	Sunderland	0	0	D	0	0	D
18/08/12	Fulham	Norwich	5	0	H	2	0	H
18/08/12	Newcastle	Tottenham	2	1	H	0	0	D
18/08/12	QPR	Swansea	0	5	A	0	1	A
18/08/12	Reading	Stoke	1	1	D	0	1	A
18/08/12	West Brom	Liverpool	3	0	H	1	0	H
18/08/12	West Ham	Aston Villa	1	0	H	1	0	H
19/08/12	Man City	Southampton	3	2	H	1	0	H

<colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody>
</tbody>

etc upto last games this season

So theres at least 5 seasons worth of football results. Output!$B$2 is a drop down box with a list of football teams.

I want select a team with Output!$B$2 and then to retrieve the teams Form (both home and away) for their LAST 12 games in the current season and have that display in the Output! worksheet in a format similar to that seen below.

Date		Half Time	Full Time
4/5/2013	Tottenham	Southampton	0	0	D	1	0	H
23/03/14	Tottenham	Southampton	1	2	A	3	2	H
5/10/2014	Tottenham	Southampton	1	0	H	1	0	H
8/5/2016	Tottenham	Southampton	1	1	D	1	2	A
3/19/2017	Tottenham	Southampton	2	0	H	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>

(this example actually uses the formula =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!$Q$2)),ROW('Historical Data'!B$2:B$9999)-ROW('Historical Data'!B$2)+1),ROWS('Historical Data'!B$2:'Historical Data'!B2))),"") to retrieve Head to Head data of 2 teams but Im looking for a similar format for Home Teams Last 12 games)

 

[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.

Thanks M

https://www.mrexcel.com/forum/excel...-those-rows-second-worksheet.html#post4800709