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

What is the error you get?

 

[gsizelove]

It is #N/A

 

[Aladin Akyurek]

It is #N/A

This looks like a consequence of unequally sized ranges. You probably made a mistake while naming the ranges. Would you check whether this is the case? (Note. The MATCH bit is ok, that is, it matches a range against itself as intended.).

 

[gsizelove]

I double checked the name manager and the ranges go through the same rows, $2:$3000. I made sure that each one named range is entered in the formula at the correct place. I was trying to focus on the first formula in cell A9 since that one is shorter and returns the error.

=SUM(IF(FREQUENCY(IF(Account<>"", IF((FSLine=$O$4)*(Company=$O$5)*(Pool=$O$6), MATCH(Account,Account,0))),Ivec),1))

I changed Crange to Account, Frange to FSLine, Grange to Company and Lrange to Pool. I also deleted a column in the B (KCO) sheet so the absolute references changed to column O instead of P.

Going through the evaluate formula function, the only time it returns an #N/A is when it reaches the second Account range in the MATCH function. My data in the Account range only runs through row 1431, but the account range is set to 3000. I did that so I wouldn't have to update the formulas if the data is extended. The #N/A is returned from row 1432 to 3000 because there is no data on those rows. Is this the problem?

Thanks again.

 

[Aladin Akyurek]

I double checked the name manager and the ranges go through the same rows, $2:$3000. I made sure that each one named range is entered in the formula at the correct place. I was trying to focus on the first formula in cell A9 since that one is shorter and returns the error.

=SUM(IF(FREQUENCY(IF(Account<>"", IF((FSLine=$O$4)*(Company=$O$5)*(Pool=$O$6), MATCH(Account,Account,0))),Ivec),1))

I changed Crange to Account, Frange to FSLine, Grange to Company and Lrange to Pool. I also deleted a column in the B (KCO) sheet so the absolute references changed to column O instead of P.

Going through the evaluate formula function, the only time it returns an #N/A is when it reaches the second Account range in the MATCH function. My data in the Account range only runs through row 1431, but the account range is set to 3000. I did that so I wouldn't have to update the formulas if the data is extended. The #N/A is returned from row 1432 to 3000 because there is no data on those rows. Is this the problem?

Thanks again.

Did you also adjust Ivec? It should now be:

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

Does this resolve the trouble?

 

[gsizelove]

Yes, Ivec was already updated with the Account name.

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

 

[gsizelove]

Sorry, I might not have been clear earlier. That name was already updated so I am still receiving the error.

 

[Aladin Akyurek]

Sorry, I might not have been clear earlier. That name was already updated so I am still receiving the error.

Would you ran the following formulas and report back the results?

=COUNTIFS(Account,#N/A)

=COUNTIFS(FSLine,#N/A)

=COUNTIFS(Company,#N/A)

=COUNTIFS(Pool,#N/A)

 

[gsizelove]

Would you ran the following formulas and report back the results?

=COUNTIFS(Account,#N/A)

=COUNTIFS(FSLine,#N/A)

=COUNTIFS(Company,#N/A)

=COUNTIFS(Pool,#N/A)

There were some #N/A values in the Pool range. I fixed those and it solved the #N/A errors. Thank you!

I do have another issue. It is listing repeating values. The data set includes multiple rows where all the three conditions will be the same. Is there a way to eliminate these?

Thanks again!

 

[Aladin Akyurek]

There were some #N/A values in the Pool range. I fixed those and it solved the #N/A errors. Thank you!

You are welcome.

I do have another issue. It is listing repeating values. The data set includes multiple rows where all the three conditions will be the same. Is there a way to eliminate these?

Thanks again!

The formulas I provided gives you a unique list of accounts. So, I fail to understand what you are trying to say here?