Array Formula - More than one wildcard in lookup

[DeanP]

I am using an array formula to find all the rows with a partial text match to a wildcard.

Is it possible:
(a) to use more than 1 wildcard in the formula;
(b) if a certain value is found, return the value of the adjacent cell.

The additional wildcards I want to use are: Assessment, SPS, Volume and Shared

If value "50" is found return value in adjacent cell.

This is my formula:

{=IFERROR(INDEX('Opex YTD'!$A$2:$A$995,SMALL(IF(ISNUMBER(SEARCH("*occupa*",'Opex YTD'!$D$2:$D$995)),ROW('Opex YTD'!$A$2:$A$995)-ROW($A$1)+1),ROWS($J$9:J52))),"End of list")}

[Paul_Hossler]

That's the sort of stuff that caused me to learn to write my first user defined function

Does it have to be an worksheet array formula?

[DeanP]

In this case it does have to be a worksheet array formula.