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.

 

[luke6567]

@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))),"")

Thank you very much for your reply!

The table is still just returning one line of data, and its still giving me an account code which isnt in the D1:D7 range.

I'm not sure why this is happening?

 

[Aladin Akyurek]

Thank you very much for your reply!

The table is still just returning one line of data, and its still giving me an account code which isnt in the D1:D7 range.

I'm not sure why this is happening?

Does D1:D7 house text values? How about K2:K350?

 

[luke6567]

Does D1:D7 house text values? How about K2:K350?

They both hold account numbers so things like 1000.2580

 

[Aladin Akyurek]

They both hold account numbers so things like 1000.2580

You probably have stray spaces around the entries of interest...

Does the following fare better?

Control+shift+enter and copy down:

=IFERROR(INDEX('Prepayments data'!A$2:A$350,
  SMALL(IF(ISNUMBER(MATCH(TRIM('Prepayments data'!$K$2:$K$350),
   TRIM('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))),"")

 

[luke6567]

You probably have stray spaces around the entries of interest...

Does the following fare better?

Control+shift+enter and copy down:

=IFERROR(INDEX('Prepayments data'!A$2:A$350,
  SMALL(IF(ISNUMBER(MATCH(TRIM('Prepayments data'!$K$2:$K$350),
   TRIM('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))),"")

Thank you so much!

That works

 

[luke6567]

You probably have stray spaces around the entries of interest...

Does the following fare better?

Control+shift+enter and copy down:

=IFERROR(INDEX('Prepayments data'!A$2:A$350,
  SMALL(IF(ISNUMBER(MATCH(TRIM('Prepayments data'!$K$2:$K$350),
   TRIM('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))),"")

Hi Aladin,

I'm also attempting to do exactly the same thing with a different tab and set of data but using the same lookup values in D1:D7.

I've changed all instances of the formula for the prepayments data tab and the criteria of the K column and input the accruals data tab and the criteria column of J, yet its decided to fail again.

Its giving the one row where the criteria column is outside the D1:D7 and then gives blanks from there on out.

The code I've got is literally just your one copy pasted with those small changes.

=IFERROR(INDEX('Accruals Data'!A$2:A$350,
SMALL(IF(ISNUMBER(MATCH(TRIM('Accruals Data'!$J$2:$J$250),
TRIM('Calender & Acc codes'!$D$1:$D$7),0)),
ROW('Accruals Data'!A$2:A$350)-ROW('Accruals Data'!A$2)+1),
ROWS(A$3:A3))),"")

 

[Aladin Akyurek]

Hi Aladin,

I'm also attempting to do exactly the same thing with a different tab and set of data but using the same lookup values in D1:D7.

I've changed all instances of the formula for the prepayments data tab and the criteria of the K column and input the accruals data tab and the criteria column of J, yet its decided to fail again.

Its giving the one row where the criteria column is outside the D1:D7 and then gives blanks from there on out.

The code I've got is literally just your one copy pasted with those small changes.

=IFERROR(INDEX('Accruals Data'!A$2:A$350,
SMALL(IF(ISNUMBER(MATCH(TRIM('Accruals Data'!$J$2:$J$250),
TRIM('Calender & Acc codes'!$D$1:$D$7),0)),
ROW('Accruals Data'!A$2:A$350)-ROW('Accruals Data'!A$2)+1),
ROWS(A$3:A3))),"")

Probably a typo... It should be:

=IFERROR(INDEX('Accruals Data'!A$2:A$350,
SMALL(IF(ISNUMBER(MATCH(TRIM('Accruals Data'!$J$2:$J$350),
TRIM('Calender & Acc codes'!$D$1:$D$7),0)),
ROW('Accruals Data'!A$2:A$350)-ROW('Accruals Data'!A$2)+1),
ROWS(A$3:A3))),"")

 

[luke6567]

Probably a typo... It should be:

=IFERROR(INDEX('Accruals Data'!A$2:A$350,
SMALL(IF(ISNUMBER(MATCH(TRIM('Accruals Data'!$J$2:$J$350),
TRIM('Calender & Acc codes'!$D$1:$D$7),0)),
ROW('Accruals Data'!A$2:A$350)-ROW('Accruals Data'!A$2)+1),
ROWS(A$3:A3))),"")

You're a godsend, thank you!

Just out of curiosity, why did that 3 make a difference? I would have thought it would have still worked until it got to row 251?

 

[Aladin Akyurek]

You're a godsend, thank you!

You are welcome.

Just out of curiosity, why did that 3 make a difference? I would have thought it would have still worked until it got to row 251?

Matching J2:J250 instead of J2:J350 against D1:D7 means that you miss almost 100 items. Apparently, the last 100 items of J deliver the most of the hits. (Note that the MATCH expression matches all of the items in the J range at once against the D range, not one by one.)

 

[luke6567]

Matching J2:J250 instead of J2:J350 against D1:D7 means that you miss almost 100 items. Apparently, the last 100 items of J deliver the most of the hits. (Note that the MATCH expression matches all of the items in the J range at once against the D range, not one by one.)[/QUOTE]

The data I was working off was only 66 rows deep, after that it was all blank.

But oh well, it works now thank you very much.