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.

 

[WebGeorge]

Hi all, great thread - helped me a lot. I'm trying to add an additional step which is to use this formula to populate a list used in Data Validation on a cell(s).
Scenario: I have a list of Projects in a table (tbl_Projects) and want to return a list of Active ones. The table has 3 columns; Project, Status, Client.
I then have anothe worksheet where I want users to enter multiple rows of data, and be able to select only Active Projects in the Project Name column. My plan is to simply setup Data Validation on the cells in that column, with the defined "List" being the formula below (via Name Manager). I've made it this far:

=IFERROR(
INDEX(tbl_Projects[Project Name],
SMALL(IF(tbl_Projects[Project Status]="Active",
ROW(tbl_Projects[Project Name])-ROW(tbl_Projects[#Headers])
),
ROWS(B$6:B6)
)
),"")


It works fine if I put the formula in a cell and fill down... but how do I get it to populate the list through validation?? I think my problem is the ROWS(B$6:B6) but can't figure out how to solve it.


Any guidance greatly appreciated!

 

[Aladin Akyurek]

Which sheet does house the formula?

 

[WebGeorge]

Which sheet does house the formula?

It's not on a sheet as such... the formula is defined in Name Manager, which means anywhere I want to use it I can just go to Data Validation / List and enter the name from Name Manager (=ActiveProjects where ActiveProjects is the name of the formula)

Does that make sense / answer the question?

Thanks

 

[Aladin Akyurek]

It's not on a sheet as such... the formula is defined in Name Manager, which means anywhere I want to use it I can just go to Data Validation / List and enter the name from Name Manager (=ActiveProjects where ActiveProjects is the name of the formula)

Does that make sense / answer the question?

Thanks

You need to create the result sublist somewhere for INDEX here must create the list row by row. Since the formula refers to ROWS(B$6:B6), I assumed that you tried out the formula in some sheet.

 

[WebGeorge]

Yeah sorry - I created the formula in a sheet first because I used the examples from this thread to build it... and I'm trying to work out how to get it "off" the sheet and into a reference I can use to build the list.

So are you saying I need to actually have another list created somewhere first for each combination and then do the data validation against those? Is there no way to generate the list in memory and direct into the Data Validation list?

Many Thanks

 

[Aladin Akyurek]

Yeah sorry - I created the formula in a sheet first because I used the examples from this thread to build it... and I'm trying to work out how to get it "off" the sheet and into a reference I can use to build the list.

So are you saying I need to actually have another list created somewhere first for each combination and then do the data validation against those? Is there no way to generate the list in memory and direct into the Data Validation list?

Many Thanks

No.

Create a sheet called Admin and run the formula you have in B9 downwards.

Define DVList by means of Formulas | Name Manager as referring to:

=Admin!$B$9:INDEX(Admin!$B:$B,MATCH("*",Admin!$B:$B,0))

Use DVList as source wherever you need the list.

 

[WebGeorge]

Thanks,

I was hoping to avoid the extra list on a worksheet but it does the trick... really appreciate your input.

Cheers

 

[Aladin Akyurek]

Thanks,

I was hoping to avoid the extra list on a worksheet but it does the trick... really appreciate your input.

Cheers

You are welcome.

 

[luke6567]

Hi all,

I know this has already been covered in the thread but I can't seem to make it work.

I'm looking to isolate and show entire rows on tab "Prepayments" from a set of data on tab Prepayment data as long as column K is any variable on tab "Calender & Acc codes" D1:D7.

The code I've currently written is:

=IFERROR(INDEX('Prepayments data'!A$2:A$350,SMALL(IF(ISNUMBER(MATCH('Prepayments data'!$K$2:$K$350='Calender & Acc codes'!$D$1:$D$7,0)),ROW('Prepayments data'!A$2:A$350)-ROW('Prepayments data'!A$2)+1),ROWS('Prepayments data'!A$2:'Prepayments data'!A2))),"")

Any help would be appreciated. All I can make it show is nothing or one line of data that isn't one of the account codes from D1:D7 on the calender and acc codes page

 

[Aladin Akyurek]

@luke6567

Source: Prepayments data.
Destination: Sheet1 (Adjust to suit)

A2 of Sheet1, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('Prepayments data'!A$2:A$350,
  SMALL(IF(ISNUMBER(MATCH('Prepayments data'!$K$2:$K$350,
   'Calender & Acc codes'!$D$1:$D$7,0)),
  ROW('Prepayments data'!A$2:A$350)-ROW('Prepayments data'!A$2)+1),
  ROWS(A$2:A2))),"")