Need excel formulas to return values for partial text matches

I am looking for some excel formulas which look for partial text matches in cells and return specific values from a lookup table. I've tried to outline what I am looking for in the spreadsheet attached. This will be a big spreadsheet with hundreds of thousands of cells in column A to group into multiple categories (probably 5 - 6 total).

I am open to any and all options! Really appreciate your help.

I see the file but which colums is should be the results?