Insert Search (wildcard) in an index/match formula

Hello All,

I figured out how to put in two conditions to my index/match formula. But now I need to complicate it further. The formula below, retrieves the "Staff" from the Master List (Column A) if that person is in "Team" LR (Column B) and has a status "Contract" (Column L). However, "Team" may have different variations. LR-Del, LR-Child, LR-Parent, etc. I tried to put in a wildcard the LR but that didn't work (not possible with If statements). Then I tried the Search() but I can't insert it in the formula correctly. Is there a better way to do this? If Search is the correct function, how/where do I insert it in the follow formula?

=IF(ISERROR(INDEX('Master list'!$A$1:$AD$278,SMALL(IF(('Master list'!$B$1:$B$278="LR") * ('Master list'!$L$1:$L$278="Contract"),ROW('Master list'!$B$1:$B$278)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$C$278,SMALL(IF(('Master list'!$B$1:$B$278="LR") * ('Master list'!$L$1:$L$278="Contract"),ROW('Master list'!$B$1:$B$278)),ROW(1:1)),1))

Thanks

Zeda

Mr. Valko,

Yes, yes, that worked!

=IF(ISERROR(INDEX('Master list'!$A$1:$AD$278,SMALL(IF((LEFT('Master list'!$B$1:$B$278,2)="LR") * ('Master list'!$L$1:$L$278="Contract"),ROW('Master list'!$B$1:$B$278)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$C$278,SMALL(IF((LEFT('Master list'!$B$1:$B$278,2)="LR") * ('Master list'!$L$1:$L$278="Contract"),ROW('Master list'!$B$1:$B$278)),ROW(1:1)),1))

Thank You

Zeda