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

CobraWave

New Member
Joined
Mar 28, 2014
Messages
3
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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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.
 
Upvote 0
Marcelo, I have a follow up question.

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

Capture.jpg


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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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),...)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,693
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top