Index/match type function that matches partial entry with a data set and spits out the general category

[pailmonkey]

I ran into another issue while trying to solve something else. I have a table of subcategories and general categories that i would like to be able to match to as close as possible to the general category. There are two parts to this issue. One is that the cell where the table will be matching is not uniform. I am exporting data from multiple companies and each one has slight variations to how they display the data. What i am trying to do is after you import the data for the spreadsheet either match directly to the general category or to match the sub category and then match to the general. Due to all of the variations there could be, i figured that matching to the sub category and then doing a simple vlookup could work but i cannot seem to get it to work.

Below is the data.
Data Import Sheet


The Cause of Injury is the data set that will be imported from company to company. The Cause Of Injury category is what the general category should be. What i am trying to do is automate as much as possible the matching if i import a data set to a corresponding general category. For example, one set of data may have "Fall, Slip, or Trip into Opening" another may be "Fall or Slip in to Openings" or "Fall/Trip into Openings" or some variation. I want to recognize that this means "Fall, Slip, or Trip Injury Into Openings" and then spit out "Fall, Slip, or Trip Injury" in the Cause of injury Category Lookup solution. Or just know that this variation falls into the general category "Fall, Slip, or Trip Injury". What is the best way to solve this? It sounds almost like need excel to match my requested cell with the sub to as similar as possible and then spit out the general. Like the text that you entered is 80% similar to this cell in the list. Then it will display the general category from the category list in the Cause of Injury Gen Category column.

Here is the entire table of possible and their general category. There are many sub categories to a general categories but the bigger issue is the variance between company to company to how they enter the subcategories. Either i manually enter the General Category with each cell or somehow automate it.

Category List

 

[Fluff]

Cross posted https://stackoverflow.com/questions...hes-partial-entry-with-a-data-set-and-spits-o

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.