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


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.


This question generated 69 answers. To proceed to the answers, click here.

This thread is current as of March 28, 2014.


For more resources for Microsoft Excel