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.

 

[Aladin Akyurek]

I would just show you the file but I can't. It has confidential information dealing with sales.

In order i will show you their defined names:
='Current Month'!$B$2:$B$2999
='Current Month'!$O:$O
=Main!$B$7
='Current Month'!$AA:$AA

Ok. Are you sure you implement this formula of yours

=IF(J10<(NbrOfRows+1),(IFERROR(INDEX(Club_NbrCM,(SMALL(IF(UPC=UPC_C,IF(QtrAvgDol<$K$6,(ROW(Club_NbrCM)-ROW('Current Month'!B$3)+1))),ROWS('Current Month'!B$3:'Current Month'!B3)))),” “))," ")

in B2 of Current Month?

ok no ... i get what you are saying now. that formula is in A10 of Main. (the display page that pulls the data.)

The ranges you refer in the formula you invoke are unequally sized.
In what follows they are corrected for that...

Main

A10, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('Current Month'!$B$2:$B$2999,
   SMALL(IF('Current Month'!$O$2:$O$2999=$B$7,
   IF('Current Month'!$AA$2:$AA$2999 < K$6,
   ROW('Current Month'!$B$2:$B$2999)-ROW('Current Month'!$B$2)+1)),
   ROWS($A$10:A10))),"")

 

[gsizelove]

I have been reading this thread and it has really helped me with an array formula. I have one that is giving me trouble so I was hoping you could help.

=IFERROR(INDEX(Sheet1!C$2:C$3000,SMALL(IF((Sheet1!F$2:F$3000='B (KCO)'!$P$4)*(Sheet1!G$2:G$3000='B (KCO)'!$P$5)*(Sheet1!L$2:L$3000=$P$6),ROW(Sheet1!C$2:C$3000)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!B$2:Sheet1!B3))),"")

When I add the bold section for a third criteria it does not work. Ideally, I need it to list values based on the three criteria and not repeat duplicates if possible.

Can you please tell me what I am doing wrong?

Thanks

Greg

 

[Aladin Akyurek]

@gsizelove

In which cell of which sheet do you enter this formula?

 

[gsizelove]

@gsizelove

In which cell of which sheet do you enter this formula?

It is entered in cell A10 of the 'B (KCO)' sheet.

 

[Aladin Akyurek]

It is entered in cell A10 of the 'B (KCO)' sheet.

A9, just enter:

=COUNTIFS(Sheet1!F$2:F$3000,$P$4,Sheet1!G$2:G$3000,$P$5,Sheet1!L$2:L$3000,$P$6)

If A9 displays a 0, we have to conclude that one or more conditions do not hold for the data.

A10, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$10:A10)<=$A$9,INDEX(Sheet1!C$2:C$3000,
   SMALL(IF(Sheet1!F$2:F$3000=$P$4,IF(Sheet1!G$2:G$3000=$P$5,
   IF(Sheet1!L$2:L$3000=$P$6,ROW(Sheet1!C$2:C$3000)-ROW(Sheet1!C$2)+1))),
   ROWS(ROWS($A$10:A10))),"")

 

[Marcelo Branco]

Aladin,

I think there is a typo in the end of your formula

....,ROWS(ROWS($A$10:A10))),"")

Shouldn't it be?
...,ROWS($A$10:A10))),"")

M.

 

[Aladin Akyurek]

Aladin,

I think there is a typo in the end of your formula

....,ROWS(ROWS($A$10:A10))),"")

Shouldn't it be?
...,ROWS($A$10:A10))),"")

M.

Yes.

 

[gsizelove]

Here are the returned values I get back. The first number is the first in the data set that meets the criteria so the formula is working. I'm just wondering if there is a way to remove the duplicates. Is that possible? There are a total of five rows with the number "0900-007" that meet the other criteria so it is not listing them all.

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
1000-012
1000-014
1000-015
1000-016
1000-017
1000-019
1000-020
1000-020

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

Thanks for any help you can offer.

 

[Aladin Akyurek]

Here are the returned values I get back. The first number is the first in the data set that meets the criteria so the formula is working. I'm just wondering if there is a way to remove the duplicates. Is that possible? There are a total of five rows with the number "0900-007" that meet the other criteria so it is not listing them all.
[...]
Thanks for any help you can offer.

Name Sheet1!C$2:C$3000 as Crange, Sheet1!F$2:F$3000 as Frange, Sheet1!G$2:G$3000 as Grange, and Sheet1!L$2:L$3000 Lrange using the Name Box or Formulas | Name Manager.

Make them for more descriptive of your data though.

Define also Ivec using Name Manager as referring to:

=ROW(Crange)-ROW(INDEX(Crange,1,1))+1

A9, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Crange<>"",
   IF((Frange=$P$4)*(Grange=$P$5)*(Lrange=$P$6),
   MATCH(Crange,Crange,0))),Ivec),1))

A10, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$10:A10)<=$A$9,INDEX(Crange,SMALL(IF(FREQUENCY(IF(Crange<>"",
   IF((Frange=$P$4)*(Grange=$P$5)*(Lrange=$P$6),
   MATCH(Crange,Crange,0))),Ivec),Ivec),ROWS($A$10:A10))),"")

 

[gsizelove]

I get an error in both A9 and A10. Can the lookup value and lookup array in the MATCH Function of cell A9 be the same range of cells?

Thanks for helping me with this.