Thanks:  0
Likes:  0

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

1. ## 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.

2. ## 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.

3. ## 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. ## Re: Extract a List of Values Filtered by Criteria with Sub-Arrays Tutorial Help

Thanks Marcelo, much appreciated.

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

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

M.

6. ## 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. ## 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. ## 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.

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

Originally Posted by Marcelo Branco
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),...)

10. ## 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.

## User Tag List

#### Posting Permissions

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