# 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

• 10.5 KB Views: 1

#### Fluff13

##### Active Member
=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

• 10.4 KB Views: 8

#### Fluff13

##### Active Member
Forgot to mention, on the Conv sheet you are missing the hyphens in H3:I3

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