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]

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.

Care to elaborate a bit more? What is the formula you attempted?

 

[Azatherazza]

I wish I could attach my worksheet, is it possible i can't find an attachment option in this forum?

My formula currently is:

{=IFERROR(INDEX(Master!A$4:A$576,SMALL(IF(Master!A$4:A$576=$B$2,ROW(Master!A$4:A$575)-ROW(Master!A$4)+1),ROWS(Master!A$4:A4)))," ")}

So in the column range MasterA4-A576 there is a list of depots (BH, CV, MV, EB, MU), the IF formula says if the row in the range MasterA4-A576 shows BH (BH is currently typed in cell B2) they return BH if it shows blank or any of the other depots (CV, MV etc...) then it skips that row until it finds the next BH in the range. I drag this down and it displays all the BH's in my master range.

What i want it to say now is if the cell has any depot displayed then return that result, if it is blank then skip that row..

 

[Aladin Akyurek]

I wish I could attach my worksheet, is it possible i can't find an attachment option in this forum?

My formula currently is:

{=IFERROR(INDEX(Master!A$4:A$576,SMALL(IF(Master!A$4:A$576=$B$2,ROW(Master!A$4:A$575)-ROW(Master!A$4)+1),ROWS(Master!A$4:A4)))," ")}

So in the column range MasterA4-A576 there is a list of depots (BH, CV, MV, EB, MU), the IF formula says if the row in the range MasterA4-A576 shows BH (BH is currently typed in cell B2) they return BH if it shows blank or any of the other depots (CV, MV etc...) then it skips that row until it finds the next BH in the range. I drag this down and it displays all the BH's in my master range.

What i want it to say now is if the cell has any depot displayed then return that result, if it is blank then skip that row..

If B2 has a value, return records related to that value. That's ok. But, what do you want to happen if B2 is empty/blank?

 

[Azatherazza]

B2 is just a random cell on a sheet seperate from the table of data I currently used to create a list for one depot, in this example BH. So if I wanted to create a seperate list for another depot i would put CV in say cell $C$2 and change that one input to look for only CV's.

if I put all the ranges in say B2:F2 (B2=BH, C2 = CV, D2=MV etc...) I want the formula to to say if the cell selected in MasterA$4:A$576= anything in B2:F2 then display whats in the cell, the only other possiblity is that if the cell selected in the master range is blank in this case I want the formula to skip the blank row until it finds the next row which has one of the depot indicators in it.

To answer your question if i put B2 blank it would return an output for only the rows that had no information and skip all the rows with depot indicators in them (BH MV etc...) which would result in a complete blank list as the output rows would output a the blank cell as blank

 

[Azatherazza]

Thanks for your time on the issue but I believe I have solved my problem. It probably a messy way of doing it but I added a new column into my data table that fed off the MasterA4:A576 range using a multiple IF criteria to convert depot indicators into numbers (eg. BH=1, MV=2 and blank =0) I then changed my array formula to

{=IFERROR(INDEX(Master!A$4:A$576,SMALL(IF(Master!B$4:B$576>0,ROW(Master!A$4:A$575)-ROW(Master!A$4)+1),ROWS(Master!A$4:A4)))," ")}

The bold now reads to column B as this is the new column using an IF formula to convert depot indicators into numbers. Any thing now with depot indicator has a value over 0 and it skips the blanks.. Yipeeeeeeee

 

[Aladin Akyurek]

B2 is just a random cell on a sheet seperate from the table of data I currently used to create a list for one depot, in this example BH. So if I wanted to create a seperate list for another depot i would put CV in say cell $C$2 and change that one input to look for only CV's.

if I put all the ranges in say B2:F2 (B2=BH, C2 = CV, D2=MV etc...) I want the formula to to say if the cell selected in MasterA$4:A$576= anything in B2:F2 then display whats in the cell, the only other possiblity is that if the cell selected in the master range is blank in this case I want the formula to skip the blank row until it finds the next row which has one of the depot indicators in it.

To answer your question if i put B2 blank it would return an output for only the rows that had no information and skip all the rows with depot indicators in them (BH MV etc...) which would result in a complete blank list as the output rows would output a the blank cell as blank

The formula you have tests the A range for equality to B2 and returns results from the same range. That doesn't look very useful...

Perhaps the following link might help to clarify your own problem:
http://www.mrexcel.com/forum/excel-questions/231882-vlookup-return-all-matches.html#post1133932

 

[Azatherazza]

I can understand why you would think that and you are correct with the outcome I was looking for. If I could show you my now completed excel spreadsheet it may be easier to understand my reasoning behind my actions.

Essentially all i was aiming to do was have report worksheets password protected so all management now ever has to do is update the master table and the password protected reports will always update and display automatically with management no longer having a need to constantly modify and update their formulas, which is why they asked me to do it.

Their was a dual purpose for the formula with 2 reports on 2 seperate worksheets display the table data slightly differently. The main issue I had was say there was a list of 200 different vehicles in our fleet and 1 is disposed but we need it to remain in a row in the master data table with the disposal date. Say that disposed vehicle was in row 150 of 200...The formula allows that row in the report to remain skipped so no blank rows show. Before we had vlookups and a numerical reference for each vehicle in the fleet. every time one was disposed or changed depot they would have to change all the formulas and renumber all the lookups. This no longer needs to occur, very happy!

 

[Aladin Akyurek]

I can understand why you would think that and you are correct with the outcome I was looking for. If I could show you my now completed excel spreadsheet it may be easier to understand my reasoning behind my actions.

Essentially all i was aiming to do was have report worksheets password protected so all management now ever has to do is update the master table and the password protected reports will always update and display automatically with management no longer having a need to constantly modify and update their formulas, which is why they asked me to do it.

Their was a dual purpose for the formula with 2 reports on 2 seperate worksheets display the table data slightly differently. The main issue I had was say there was a list of 200 different vehicles in our fleet and 1 is disposed but we need it to remain in a row in the master data table with the disposal date. Say that disposed vehicle was in row 150 of 200...The formula allows that row in the report to remain skipped so no blank rows show. Before we had vlookups and a numerical reference for each vehicle in the fleet. every time one was disposed or changed depot they would have to change all the formulas and renumber all the lookups. This no longer needs to occur, very happy!

Not sure I follow. If the link does not help at all, please post a very small sample long with the output that you desire to obtain.

 

[Trevor S]

Is it possible to use an OR statement inside the IF statement? I am trying to return rows that have criteria that meet either of two values. Basically, return all rows that equal X or Y in the criteria column of the original data set. When I put an OR statement inside the IF statement I now get all rows returned (i.e. those that have Z in the criteria column). I pased my formula below and bolded the OR statment section. I want it to return only those rows that equal A2 or A3. Any help would be great. Thanks!

=IFERROR(INDEX('Project Portfolio'!A$3:A$83,SMALL(IF(

OR('Project Portfolio'!$K$3:$K$83=$A$2,'Project Portfolio'!$K$3:$K$83=$A$3),

ROW('Project Portfolio'!A$3:A$83)-ROW('Project Portfolio'!A$3)+1),ROWS('Project Portfolio'!A$3:'Project Portfolio'!A3)))," ")

 

[Aladin Akyurek]

Is it possible to use an OR statement inside the IF statement? I am trying to return rows that have criteria that meet either of two values. Basically, return all rows that equal X or Y in the criteria column of the original data set. When I put an OR statement inside the IF statement I now get all rows returned (i.e. those that have Z in the criteria column). I pased my formula below and bolded the OR statment section. I want it to return only those rows that equal A2 or A3. Any help would be great. Thanks!

=IFERROR(INDEX('Project Portfolio'!A$3:A$83,SMALL(IF(

OR('Project Portfolio'!$K$3:$K$83=$A$2,'Project Portfolio'!$K$3:$K$83=$A$3),

ROW('Project Portfolio'!A$3:A$83)-ROW('Project Portfolio'!A$3)+1),ROWS('Project Portfolio'!A$3:'Project Portfolio'!A3)))," ")

A4, control+shift+enter, not just enter, and copy down

=IFERROR(INDEX('Project Portfolio'!A$3:A$83,
  SMALL(IF(ISNUMBER(MATCH('Project Portfolio'!$K$3:$K$83=$A$2:$A$3,0)),
  ROW('Project Portfolio'!A$3:A$83)-ROW('Project Portfolio'!A$3)+1),
  ROWS($A$4:A4))),"")

Note that the formula starts in A4 and the last term is a blank, not a space (that is, "", not " ").