Thanks Thanks:  0
Likes Likes:  0
Page 1 of 14 12311 ... LastLast
Results 1 to 10 of 132

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

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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:

    Code:
    =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.
    Last edited by CobraWave; Mar 28th, 2014 at 04:30 PM.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    Hi and welcome to Mr Excel

    Some thoughts
    1. In array formulas logical operators as AND, OR work differently.
    To build an AND condition in array formulas you must use nested IFs like
    IF(condition1,IF(condition2....

    Or you can multiply the conditions
    IF((condition1)*(condition2),...

    2. As you are inserting the formulas in the sheet Filtered List is not necessary to use the sheet name when refering to cells in that sheet. ie, you should use $A$2 instead of
    'Filtered List'!$A$2

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

    confirmed with Ctrl+Shift+Enter

    copy across and down

    M.
    Last edited by Marcelo Branco; Mar 28th, 2014 at 05:30 PM.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    Forgot to say

    As the first formula is being inserted in cell A5 the formula should be adjusted as below (see in blue)

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

    M.

  4. #4
    New Member
    Join Date
    Mar 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks Marcelo, much appreciated.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by CobraWave View Post
    Thanks Marcelo, much appreciated.
    You are welcome and thanks for the feedback.

    M.

  6. #6
    New Member
    Join Date
    Mar 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Marcelo, I have a follow up question.

    I nested more if statements to create more search variables. This is what I have now:



    The user enters what criteria they're looking for in row 3, and it outputs in row 7 onward.

    The problem lies if the user does not care about one or more parameters and leaves those fields blank, then nothing is returned at all.

    I think checking if the cell values are equal or not blank would work. ie:
    (ISBLANK($A$3))

    I looked up how to computer OR in an array and got this:
    ((condition_1) + (condition_2)>0)

    So by that logic. this should work:
    IF(('Car Data'!$B$2:$B$1156=$A$3)+(ISBLANK($A$3)>0,....etc

    But it simply outputs ALL the cars in the database.

    The complete code:

    Code:
    =IFERROR(
     INDEX('Car Data'!B$2:B$1156,
      SMALL(
      IF(('Car Data'!$B$2:$B$1156=$A$3)+(ISBLANK($A$3)>0),
        IF(('Car Data'!$C$2:$C$1156=$B$3)+(ISBLANK($B$3)>0),
         IF(('Car Data'!$D$2:$D$1156=$C$3)+(ISBLANK($C$3)>0),
          IF(('Car Data'!$E$2:$E$1156=$D$3)+(ISBLANK($D$3)>0),
           IF(('Car Data'!$F$2:$F$1156=$E$3)+(ISBLANK($E$3)>0),
            IF(('Car Data'!$G$2:$G$1156=$F$3)+(ISBLANK($F$3)>0),
             IF(('Car Data'!$H$2:$H$1156=$G$3)+(ISBLANK($G$3)>0),
              ROW('Car Data'!B$2:B$1156)-ROW('Car Data'!B$2)+1))
             )
            )
           )
          )
         ),
        ROWS(A$7:A7)
       )
      ),"")
    TLDR: How do you compute OR in an array?

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    So by that logic. this should work:
    IF(('Car Data'!$B$2:$B$1156=$A$3)+(ISBLANK($A$3)>0,....etc
    See if this works
    IF(('Car Data'!$B$2:$B$1156=$A$3)+LEN('Car Data'!$B$2:$B$1156)*($A$3=""),....

    M.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    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.
    Last edited by Marcelo Branco; Mar 31st, 2014 at 03:10 PM.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,896
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by Marcelo Branco View Post
    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.
    Curious though what would be more efficient:

    1) =IF(('Car Data'!$B$2:$B$1156=$A$3)+($A$3=""),...)

    2) =IF(('Car Data'!$B$2:$B$1156=IF($A$3="",'Car Data'!$B$2:$B$1156,$A$3),...)
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi I have also followed this thread since the tutorial on another site and being relatively new to these more advanced excel formulas am completely stumped with how to finish it for the purposes I require.

    Originally the formula was providing a result from a row if the MPG was greater then 45mpg by referring to a cell with 45 in it. In my example I want to refer to multiple MPGs.. so if the mpg was 45 or 30 or 15 or 10 etc.. say 5 specific mpgs then it shows the column reference from that row in the array. Then if the column was blank it would not show it at all and skip without leaving a line space.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •