Note: for multiple conditions in that syntax wildcards won't work, you can try the SEARCH function, e.g.

=INDEX('SOURCESHEET'!C2:C100,MATCH(1,ISNUMBER(SEARCH('DESTSHEET'!A2,'SOURCESHEET'!A2:A100))*('SOURCESHEET'!B2:B100=2),0))

confirmed with CTRL+SHIFT+ENTER