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.

 

[gsizelove]

The formulas I provided gives you a unique list of accounts. So, I fail to understand what you are trying to say here?

Here are the returned values starting with cell A9.

17
0900-007
0900-007
1000-004
1000-007
1000-007
1000-007
1000-007
1000-007
1000-008
1000-008
1000-008
1000-008
1000-009
1000-009
1000-010
1000-010
1000-010

<colgroup><col></colgroup><tbody>
</tbody>

Looking at account 0900-007, there are two values in Sheet1 that have this same account, FSLine, Company and Pool. This happens other times in Sheet1 with other accounts.

 

[Aladin Akyurek]

Here are the returned values starting with cell A9.

17
0900-007
0900-007
1000-004
1000-007
1000-007
1000-007
1000-007
1000-007
1000-008
1000-008
1000-008
1000-008
1000-009
1000-009
1000-010
1000-010
1000-010

<tbody>
</tbody>

Looking at account 0900-007, there are two values in Sheet1 that have this same account, FSLine, Company and Pool. This happens other times in Sheet1 with other accounts.

Apply...

=LEN(A10)

=LEN(A11)

What are the results?

 

[gsizelove]

Apply...

=LEN(A10)

=LEN(A11)

What are the results?

Both formulas return an 8

 

[gsizelove]

I didn't copy the formulas down far enough. Thanks again for your help, it works perfectly now!!!

 

[roseraven86]

I have been following this thread and most of my uses of this sub-array work without issue. I am trying to use the sub-array to create a list of entries where the last modified date is greater than 60 days from today. The column on my baseline which shows the difference between the dates is formula driven, "=today()-O2". If I modify the sub-array to reference a different column with a static number it works just fine. Am I missing something?

Disregard my post above.

This simpler formula
IF(('Car Data'!$B$2:$B$1156=$A$3)+($A$3=""),

worked ok for me

If the user leaves a field blank all values of that field will be accepted

M.

 

[roseraven86]

I was able to find the problem and resolve the issue I was originally encountering.

But how do I now get the list to only populate with unique values? Currently I am getting several duplicates where there may be more than one row of data that matches my criteria. Here is the formula I have currently.
{=IFERROR(INDEX(Baseline!AE$2:AE$2000,SMALL(IF(Baseline!F$2:F$2000=$J$3,IF(Baseline!C$2:C$2000=LookUp!$E$2,ROW(Baseline!A$2:A$2000)-ROW(Baseline!A$2)+1)),ROWS(Baseline!A$2:Baseline!A2))),"")}

I have been following this thread and most of my uses of this sub-array work without issue. I am trying to use the sub-array to create a list of entries where the last modified date is greater than 60 days from today. The column on my baseline which shows the difference between the dates is formula driven, "=today()-O2". If I modify the sub-array to reference a different column with a static number it works just fine. Am I missing something?

 

[Aladin Akyurek]

I was able to find the problem and resolve the issue I was originally encountering.

But how do I now get the list to only populate with unique values? Currently I am getting several duplicates where there may be more than one row of data that matches my criteria. Here is the formula I have currently.
{=IFERROR(INDEX(Baseline!AE$2:AE$2000,SMALL(IF(Baseline!F$2:F$2000=$J$3,IF(Baseline!C$2:C$2000=LookUp!$E$2,ROW(Baseline!A$2:A$2000)-ROW(Baseline!A$2)+1)),ROWS(Baseline!A$2:Baseline!A2))),"")}

In which cell and in which sheet did you enter this formula? And are you wanting to replace with one that does a unique listing instead?

 

[roseraven86]

The formula is written in a new sheet (called sector summary), cell A2. I would like it to populate a single list devoid of duplicates even if that requires a new formula. Thank you!!

In which cell and in which sheet did you enter this formula? And are you wanting to replace with one that does a unique listing instead?

 

[Aladin Akyurek]

The formula is written in a new sheet (called sector summary), cell A2. I would like it to populate a single list devoid of duplicates even if that requires a new formula. Thank you!!

The formula you have references following conditions:

$J$3
LookUp!$E$2

Is J3 in 'sector summary'?

Why must the second entry in LookUp?

 

[roseraven86]

J3 is on the sector summary and is a drop down box which drives the sheet. The Lookup sheet houses entries that are used throughout the workbook so for consistency I had the formula going there for that piece. However, I can put the same field on the sector summary in J4 if it is easier.

The formula you have references following conditions:

$J$3
LookUp!$E$2

Is J3 in 'sector summary'?

Why must the second entry in LookUp?