Vlookup for finding parts of text in lookup range cells

I am trying to match a product code (number) to the product name (text) of many different products. I have a list of products and corresponding codes, but for some reason I continue to get N/A, even when I use wildcards or other types of lookup functions. The problem is that the cell I want to match code to contains only one thing of text, whereas the range might have multiple product names (e.g., If i want to find the code for pants, the range will have something like "Shirts/Socks/Pants.")

Anyone have an idea of how to incorporate a find("Pants") function in a vlookup or index match so I can find the corresponding codes?

Example workbook attached.. Thanks!

that doesn't seem to be working.

Would something like this work? =VLOOKUP(A4&B4&E4&F4,'Sheet1'!$A$2:$J$2022,3,0). It is returning plenty of values, but I am worried they aren't right.