Filter array with 2 conditions

[AllanSull]

I currently use the following formula to filter an array when the value in I2 is equal to a cell in the named range FILTERRANGE.

IFERROR(INDEX(DATA!A$2:A$40,SMALL(IF(FILTERRANGE=FILTERED!$I$2,ROW(DATA!A$2:A$40)-ROW(DATA!A$2)+1),ROWS(DATA!A$2:DATA!A2))),0)

I now want to filter the array based on 2 conditions, but inserting the AND condition will not work.

IFERROR(INDEX(DATA!A$2:A$40,SMALL(IF(AND(FILTERRANGE=FILTERED!$I$2,FILTERRANGE2=TRUE,ROW(DATA!A$2:A$40)-ROW(DATA!A$2)+1),ROWS(DATA!A$2:DATA!A2))),0)

Any help would be appreciated.

 

[shaowu459]

AND function will only return ONE value, TRUE or FALSE. you should use code like

([COLOR=#333333]FILTERRANGE=FILTERED!$I$2)*(FILTERRANGE2=TRUE)[/COLOR]