Help with table lookup using matched text

I have a somewhat complex thing I am trying to do in Excel and I have had no luck playing with Vlookups or Index Match functions I have found. Here's a basic look at what I am doing.

Sheet 1: List of codes in row 1, and keywords related to those codes in their respective columns. This list could grow as keywords are added over time, so the table array is essentially the entire sheet.

Sheet 1 looks something like this (codes on top, and keywords below them), and the underscores are just to separate cells so it looks like a spreadsheet (ignore them), the keywords I put are just placeholders (imagine they are actual department/title names that contain many words with spaces between in Sheet 2, but in Sheet 1 they will be only single words):

0001__0002__0003__0004
and___big____find___many
on____long___but___part


Sheet 2: Descriptions of departments and/or titles. Sheet 2 looks something like this:

Department____Title____Code
and on________and____
but___________but____
many part_____long____
big___________long____




*What I am trying to do is search the text strings in sheet 2 (by row) for matches in sheet 1, and return the sheet 1, row 1 code associated and have it fill in the code column in sheet 2. Essentially I want to auto-populate sheet 2 with codes based on keywords from departments and titles by simply copying a single cell code down from the first row under Codes in Sheet 2.


Ideally:
*I would like to be able to search two columns of text strings (one being departments and one being titles) - If not, I can concatenate
*I would like to be able to prioritize code lookups: so if 2 words found in the text strings from sheet 2 match one column code in sheet 1, but if three words match a separate column, the row 1 code that has more matches is returned.

I hope this is enough information for somebody top be able to help. I would ideally prefer not to use pivot-tables or VBA, but if I need to rearrange a table I could. It's also ok if I need to tweak the setup (such as extracting individual words first and then matching them). Thanks for any help.

A sample book with expected results would be of more helps to all