Search multiple cells for a multiple wildcard words

Hi I'm trying to use a list of wildcard words to search multiple cells for any of the listed words. If the searched cells contain any version of the word ie *bird* should activate with any of the following (Bird, Birds, birds, bluebird, birdbath), then I would like a yes returned. If not then either leave blank or return a no. I've got a number of formulas but they aren't quite right and I'm not very good at creating/changing formulas.

From what I've attached sheet 1 (export Dump) is just all the info dumped in. Sheet 2 (Data Tables) has the tables of words that I want to look for. Sheet 3 (Filtered Results) is the filtered version of sheet 1 and what I'm trying to figure out is codes for Columns E+F in Filtered Results, based on text in column C+D.

Right now I've got the following code for Test1 =IFERROR(LOOKUP(1,-SEARCH('Data Tables'!$D$2:$D$4,C2:D2),'Data Tables'!$D$2:$D$4),"") however it doesn't seem to be getting all the wildcard versions ie D3 (filtered Results) contains birdbath which should return a result. Also it's spitting out the stringword rather than just Yes.

I tried something a bit different with Test2 with the following code =IF(COUNTIF(C2:D2,'Data Tables'!$E$2:$E$3),"Yes","No") but it gives me a spill error.

Essentially if a formula works for test1 then it should work with test2 once I change the data table.

I hope this makes sense.

Please add a manually entered column in G that shows your EXPECTED results. Non-working formulae don't show us what we're aiming for. Thanks.

Not following this ...

Row 4: swimming has *paint* as its search term, so why should the formula pick out 'bird'???

OK, so this will get what you want for Test 1 *:

Please Login or Register  to view this content.

and this should get what you'd want for Test 2:

Please Login or Register  to view this content.

This, however, combines the two and looks at both Test 1 * and Test 2 at once, and returns YES if any of the values in either list is found:

Please Login or Register  to view this content.

* I believe that G3 should be YES - D3 contains 'birdbath'.

Here is another option:

Test 1:

Formula:

Please Login or Register  to view this content.

Test 2:

Formula:

Please Login or Register  to view this content.

Test 1 & 2 together:

Formula:

Please Login or Register  to view this content.