Array forumla - Contains rather than =

[Steven101]

I have the following array formula, which works great. My issue is the Table[Area]="Yes", can this be changed to contains "Yes". I have tried Table[Area]="*Yes*" but this doesn't work.

[CODE]=IFERROR(INDEX(Table1[Ref],SMALL(IF((Table1[Date]>=$B$2)*(Table1[Date]<=$B$3)*([B]Table1[Area]="Yes")[/B]*(Table1[HANDLER OVERALL]="U"),ROW(Table1[Date])-1,""),$A16),1),"")

[/CODE]

Any help would be appreciated.

Steven

 

[Special-K99]

Does this work (untested)?

=IFERROR(INDEX(Table1[Ref],SMALL(IF((Table1[Date]>=$B$2)*(Table1[Date]<=$B$3)*(ISNUMBER(SEARCH("Yes",Table1[Area])))*(Table1[HANDLER OVERALL]="U"),ROW(Table1[Date])-1,""),$A16),1),"")

 

[Steven101]

Perfect Special-K99, also gives me a better understanding of how the If statement works in this array!

Many Thaanks

Does this work (untested)?

=IFERROR(INDEX(Table1[Ref],SMALL(IF((Table1[Date]>=$B$2)*(Table1[Date]<=$B$3)*(ISNUMBER(SEARCH("Yes",Table1[Area])))*(Table1[HANDLER OVERALL]="U"),ROW(Table1[Date])-1,""),$A16),1),"")