Lookup Based on Multiple Criteria - with a contains option

[Steven101]

Hi I have the following code which works great based on the Role columns in a table being "Yes"


Looks up the nth occurrence of,

A5 =Table4[Name]
B1 <= Table4[Date]
B2 >= Table4[Date]
Yes = Table4[Role]

and returns the Table4[Ref].

=INDEX(Table4[Ref],SMALL(IF(($A5=Table4[Name])*($B$1<=Table4[Date])*($B$2>=Table4[Date])*(Table4[Role]="Yes"),ROW(Table4[Name])-ROW(Data!$A$2)+1),Nth))

Is it possible to amend the formula so that Table4[Ref] contains "Yes"...I have tried amending

"Yes" = Table4[Role] to "*Yes*" = Table4[Role] but does no work

Any help would be appreciated.
Thanks

 

[Marcelo Branco]

Maybe...

(ISNUMBER(SEARCH("Yes",Table4[Role])))

Hope this helps

M.

 

[Marcelo Branco]

Or, if your Excel version uses semicolon as argument separator

(ISNUMBER(SEARCH("Yes";Table4[Role])))

M.

 

[Steven101]

Thanks, works a treat!