• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formula - multiple criteria

jassybun

Member
I want to add another option to include besides "DAY" , like "NIGHT" and "EARLY" in the formula below, like this: (Conv!$L$1:$L$5000={"DAY", "NIGHT", "EARLY"}), but I cannot get it to work - I think this is because I have another multiple option in the same line: ={"1 - Over","2 - Under"}), How can I get around this?

=IF(ISERROR(INDEX(Conv!$A$1:$AX$5000,SMALL(IF((Conv!$L$1:$L$5000="DAY")*(Conv!$I$1:$I$5000>=$J$13)*(Conv!$H$1:$H$5000={"1 - Over","2 - Under"}), ROW(Conv!$D$1:$D$5000)-MIN(ROW(Conv!$D$1:$D$5000))+1,""),ROW(Conv!A1)),COLUMN(Conv!A1))),"", INDEX(Conv!$A$1:$AX$5000,SMALL(IF((Conv!$L$1:$L$5000="DAY")*(Conv!$I$1:$I$5000>=$J$13)*(Conv!$H$1:$H$5000={"1 - Over","2 - Under"}),ROW(Conv!$D$1:$D$5000)-MIN(ROW(Conv!$D$1:$D$5000))+1,""),ROW(Conv!A1)),COLUMN(Conv!A1)))
 

Attachments

Fluff13

Member
How about
=IF(ISERROR(INDEX(Conv!$A$1:$AX$5000,SMALL(IF((Conv!$L$1:$L$5000={"DAY","NIGHT","EARLY"})*(Conv!$I$1:$I$5000>=$J$13)*((Conv!$H$1:$H$5000="1 - Over")+(Conv!$H$1:$H$5000="2 - Under")), ROW(Conv!$D$1:$D$5000)-MIN(ROW(Conv!$D$1:$D$5000))+1,""),ROW(Conv!B2)),COLUMN(Conv!B2))),"", INDEX(Conv!$A$1:$AX$5000,SMALL(IF((Conv!$L$1:$L$5000={"DAY","NIGHT","EARLY"})*(Conv!$I$1:$I$5000>=$J$13)*((Conv!$H$1:$H$5000="1 - Over")+(Conv!$H$1:$H$5000="2 - Under")), ROW(Conv!$D$1:$D$5000)-MIN(ROW(Conv!$D$1:$D$5000))+1,""),ROW(Conv!B2)),COLUMN(Conv!B2)))
 

jassybun

Member
I tried it that way - didn't work - I am attaching it again with your suggestion

I have also simplified the formula:

=IFERROR(INDEX(Conv!$A$1:$AX$5000,SMALL(IF((Conv!$L$1:$L$5000={"DAY","NIGHT"})*(Conv!$I$1:$I$5000>=$J$13)*((Conv!$H$1:$H$5000="1 - Over") +(Conv!$H$1:$H$5000="2 - Under")), ROW(Conv!$D$1:$D$5000)-MIN(ROW(Conv!$D$1:$D$5000))+1,""),ROW(Conv!A1)),COLUMN(Conv!A1)),"")
 

Attachments

Peter Bartholomew

Well-Known Member
I apologise but I had to convert the data to a table in order to read it; that's just me!

Code:
=  INDEX(Table1,
    SMALL(
        IF(((Table1[column.l]="DAY")+(Table1[column.l]="NIGHT"))*
            (Table1[column.i]>=date)*
            ((Table1[column.h]="1 - Over") +(Table1[column.h]="2 - Under")),
            ROW(Table1)-ROW(Table1[#Headers]),""),
        ROW(Conv!A2)),
    COLUMN(Conv!A2))

I think the problem was with the test for {"DAY","NIGHT"} which created a two column array that was never reduced back to a single column with +.
 
Top