Find specific text within text and result dependent text in specified column

With the tables defined you could use a SEARCH approach, e.g.:

Formula:

B2: =LOOKUP(9.99E+307,SEARCH($G$2:$G$6,$A2),$H$2:$H$6)

C2: =LOOKUP(9.99E+307,SEARCH($I$2:$I$3,$A2),$J$2:$J$3)

D2: =LOOKUP(9.99E+307,SEARCH($K$2:$K$10,$A2),$L$2:$L$10)

B2:D2 copied down

note: pending your client settings you may need to modify delimiters (and function names) to your reflect your locale

EDIT: if you should need to generate different outputs for strings that are not mutually exclusive, e.g. REQUIREMENT & REQUIREMENTS, you would need to adapt the above -- however your samples would imply this is not an issue.

No, this is not an Array formula - so can be entered with usual Enter command.

9.99E+307 is a very big number....

LOOKUP will find the last value in the lookup vector that is <= criteria, and will only consider values in the lookup vector that are of the same data type as the criteria {and, importantly, ignores errors in this context}

In this example the SEARCH will return an array of values, one per search value, and these values will either be a number (if search string is found), or #VALUE! (not found)

The LOOKUP finds the last number in the lookup vector (given our criteria is a number, and will be bigger than any number in the lookup vector) and subsequently return the associated value from the (optional) result vector -- in this case the the return columns in your three tables.

In case you are having locale issues translating the suggested formula I have attached the same which will auto translate when opened on your client.

I hope that helps

edit: I should have added that LOOKUP assumes that the lookup vector is sorted -- however when used in this context it's effectively irrelevant