Lookup for Code that Corresponds to Part of string of text in a Cell

I am trying to find a function to lookup a code assigned with a string of text, e.g., connect that code 0851 corresponds with "Black Hat." The problem is that the table that I would normally use the vlookup for has multiple strings of text in the cell, so it won't find "Black Hat" since the cells go something like "Black Hat/Purple Shoes." Is there a function that will help me answer "If 'Black Hat' is contained in a cell, show me the code that corresponds to that cell which contains 'Black Hat'"?

Hopefully that is clear. I was having trouble trying to figure out how I would ask. Basically, I am looking to see how I can identify a part of the text in a cell (almost like doing ctrl + F and typing in part of the word but still finding what you are looking for).

Yes, but the problem is that I am dealing with about 500 rows, all of which contain text in the cell. I would usually do VLOOKUP(A3, "Worksheet1" A4:F300, 3, FALSE), but A3 here is text, so A3= Black Hat, A4 = Yellow Shirt, A5 = Green Pants, A300 = Brown Shoes. I need something that looks at A3 and then finds the cell with "Black Hat/Hard Floor/etc etc" and it identifies that the cell contains Black Hat and returns the code in the cell related to it.

Thanks!

It's giving me #N/A

Update: The following function works for about 20% of the rows in my worksheet

=VLOOKUP("*"&B230&"*",'Code Names'!$B$2:$C$58,2,FALSE)

The problem is that if the cell in column B is, say, Program Assistant, it will show up as N/A because it won't find this in a cell that has "Program Director/Program Assistant", i.e., it won't find Program Assistant if it is the second, third, etc, in a cell. It only finds it if it is "Program Assistant/Program Director"

It gives me a bunch of N/As. please see this workbook

Ok I think I'm almost there. I see what you are saying, so for example I want to find Paralegal code but the name is Paralegal Program, but only Paralegal is contained in the sheet with the corresponding code -- how do I modify the lookup value so that is only looks for "Paralegal" instead of requiring the cell to contain the full name "Paralegal program"?