Search column for keywords and add label

Excel version: 2016, separator is “;” instead of “,” (no XLOOKUP available) 

Sample file see below

I am trying to find a Function which I can place in a helper column which displays a certain text string depending on keywords found in a column of data populated by a pivot.

So let’s break this down, in the sample file you can see the root causes of IT issues which I get after using a Pivot on a dataset.

The first column is to cluttered to use in my reporting so I would like to bring it down to a few categories using some kind of function.

The function should use a list of keywords found in Column A inside the Keywords tab. With these keywords (more keywords will be added in the future) it searches for matches in column A of the Data tab. Once a match is found it needs to fill column C of the Data tab with the correct Category. In the Keyword tab, I’ve added a column with a dropdown in a behind the Keywords so I can add a Category for each (new Categories will be added in the future). Then a second function should look at column C of the Data tab and fill in the Summary located at D44 in the Data Tab with a separate count for each category.

Thank you for your assistance