Complex INDEX and SMALL or MATCH based on muliple criteria

  • TLDR: I am fairly certain the OR function inside the array function is my problem. Is there a way to get this to work so that something like OR({FALSE,FALSE,FALSE},{TRUE,FALSE,FALSE}{FALSE,TRUE,FALSE}) will return {TRUE,TRUE,FALSE}?




    I have an excel file containing ~900 rows of information on one worksheet. Each row is designed to be represented and filtered by three categories and four options. Values for the Category are (blank), A, AB, or ABC. Values for the Option are (blank), (any combination of +, V, E, or G), or --.


    I have dropdown menus on a separate worksheet intended to allow a user to filter the complete list based on the user's desired categories and options. For example, selecting B for Category 2 will copy from the full list all rows that contain AB or ABC as the entry for Category 2. Each of the four options can be enabled or disabled. If enabled, all rows containing a +, V, E, or G for that option should be included on the list regardless of whether or not they have the B identified for Category 2. Further, any enabled Option that has a -- in the row means that row should not be included, even if the Category or another Option would otherwise add the row.


    I have an array formula written on the separate worksheet that pulls rows from the full list to the second sheet based on the selection of a single Category (#2 currently). When I attempt to modify my IF statement to include the other two categories, the function ceases to work correctly and begins to pull every row.


    I have attached a copy of my file with dummy content. Here is the function I have that works (taken from cell B10, the first and leftmost cell beneath the headers).


    {=IFERROR(INDEX('Full List'!B$10:B$26,SMALL(IF(ISNUMBER(SEARCH($D$4,'Full List'!$G$10:$G$26)),ROW('Full List'!B$10:B$26)-ROW('Full List'!B$10)+1),ROWS('Full List'!B$10:B10))),"")}


    If I attempt to add anything to the IF statement, the equation ceases to work properly. Here is the change I attempted to make. Ideally this should enable me to filter rows by all three categories.


    {=IFERROR(INDEX('Full List'!B$10:B$26,SMALL(IF(OR(ISNUMBER(SEARCH($D$3,'Full List'!$F$10:$F$26)),ISNUMBER(SEARCH($D$4,'Full List'!$G$10:$G$26)),ISNUMBER(SEARCH($D$5,'Full List'!$H$10:$H$26))),ROW('Full List'!B$10:B$26)-ROW('Full List'!B$10)+1),ROWS('Full List'!B$10:B10))),"")}


    If I could get this IF statement to work, I figure that would pave the way for me to get the rest of my functionality to also work. I imagine this would ultimately this would be a lot of OR/IF statements to check the criteria of the options and an AND/IF statement to check for the --.



    I would not consider myself an expert at Excel functions, so I'm not sure if one of the other functions the IF statement is nested into is causing the issue. Could you please help me to get this equation working with more than one criteria in my filter? Thank you very much!

  • Here's the solution! As I suspected, logical operators are disfunctional inside an array formula; they can only return a single result of TRUE or FALSE, not an array of TRUE and FALSE. Here's the workaround that worked for me:


    Instead of IF(OR((statement1),(statement2),(statement3)),...
    I used IF((statement1)+(statement2)+(statement3)>0,...


    Similar logic works for AND
    normal usage: AND((statement1),(statement2)
    array usage: (statement1)*(statement2)


    Have fun!

  • Start your own thread, plesae.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!