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.

 

[Pskadot]

Aladin,,

Thank you for yourassistance.

Would it be possible if you could help with the second formula. I followed everything and was wondering why the formula would have =IF($A3="","",INDEX(control!$A$2:$D$100,$A3,MATCH(B$2,control!$A$1:$D$1,0))) - a b$2 which is on the comment section where a1 has the filter in the comment sheet and where on the control sheet - control!$A$1:$D$1,0 would make reference to the title on the control sheet.

The results im getting is blank.

Any help would be appreciative. I followed you example and I am lost. Maybe I'm doing something wrong. The formula is exactly how you typed it and the the example is exactly how it was describe and created.

Pskadot

 

[Pskadot]

I found the error

 

[Pskadot]

aladin,

Is there a way to pull the specific text without having the text to be inbetween words as the formula is doing. Also, what If I want to modify the filter and in B1 and B2 add other words to be looked at within the paragraph of the source sheet. Any help would be great

 

[Aladin Akyurek]

aladin,

Is there a way to pull the specific text without having the text to be inbetween words as the formula is doing. Also, what If I want to modify the filter and in B1 and B2 add other words to be looked at within the paragraph of the source sheet. Any help would be great

Do you have some example text? Note that the set up has such text to look for in A1.

 

[Pskadot]

It could be in b1 c1 d1 e1 words could be test, warranty, books, keys these are just sample words but it could anything

 

[Aladin Akyurek]

It could be in b1 c1 d1 e1 words could be test, warranty, books, keys these are just sample words but it could anything

In A1 of comment (the results sheet) we have a text value (search value) we want to look for in the Comment field of control (the data sheet). In this comment sheet, we get multiple field records as result. Is this picture clear to you? I'm asking this because your mention of B1, C1, D1, and E1 is not consistent with the layout of comment.

 

[Pskadot]

Aladin,

in line with the following:

Go to Comment.

A1 houses a key word (and If i decided to add other words in B1, C1, and D1 how can I do the search with the formula looking jsut for the that specific word instead of the word being looked for in between other words)

Enter the following

Idx in A2,

control # in B2,

name in C2,

number in D2,

and comment in E2.

In A3 of Comment, control+shift+enter, not just enter, and copy down:
Code:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1&" ",control!$D$2:$D$100)), ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")</pre>
Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel will put { and } around the formula.

In B3 of Comment, just enter, copy across, and down:
Code:

=IF($A3="","",INDEX(control!$A$2:$D$100,$A3, MATCH(B$2,control!$A$1:$D$1,0)))</pre>

 

[Aladin Akyurek]

[...]

A1 houses a key word (and If i decided to add other words in B1, C1, and D1 how can I do the search with the formula looking jsut for the that specific word instead of the word being looked for in between other words)

[...]

Just to elaborate...

A1: JAD
B1: KAD

What are we looking for? JAD or KAD or both JAD and KAD at the same time?

 

[Pskadot]

Just to elaborate...

A1: JAD
B1: KAD

What are we looking for? JAD or KAD or both JAD and KAD at the same time?

Aladin,

was able to amend the formula and it see to work =IFERROR(SMALL(IF(ISNUMBER(SEARCH($A$1:$d$1,control!$D$2:$D$100)), ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")- It works for now but im just doing more test hopefully nothing else that i see im doing wrong unless you see something. Basically, with the example you provided with the words JAD and KAD the formula would pick the words up. What I'm trying to achieve is if on the Control sheet where the individual writes a comment and within those comments were the key word KAD and JAD, the formula would pull every word that the individual wrote. That is what im trying to achieve.

Also, is there a way where a within the same formula I can just pull a particular month. The Control sheet is used daily and there are no headers to distinguish the months - basically at the end of the month the next data for next month is entered below the last date of the month for example;

january 31 comments are inputted in accordance to their respective cell
feburary 01 new information is added in accordance to their respective cell and so forth

this control sheet get longers as the month passes and at the end of the month it becomes harder to prepare any type of reports without doing manual count of what was inputed per month.

Hope that helps

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(" "&$A$1:$D$1&" "control!$D$2:$D$100)), ROW(control!$D$2:$D$100)-ROW(control!$D$2)+1),ROWS($A$3:A3))),"")

I think it's better to date your data in order to solve the date related problem.