0down votefavorite
I need to format some raw data for a trend report as following:
The raw data is extracted from a ticketing system and I need to assign a Category/Topic and a Subcategory to each ticket by using the text present in the "Short description" tab. I defined two names as "keywords" and "keywordreturn" and tried to use them with the following formula: "=LOOKUP(9.99999999999999E+307,SEARCH(keywords,AL2),keywordreturn)"
It works just fine, with one exception: For one of the Topics (category1), named MS Office (should find the tickets related to MS Office, using keywords as "word" and "excel") I get false pozitives: this category is triggered by the keyword "Password", which should normally be assigned to the "Account" Topic/Category, as visible in the screenshot. My question is: why does it happen and how can I remediate it? Also, is there any other formula to use for this?
Here it's how it looks like
I must mention that I coundn't find anything to define Password as a keyword for MS Office and I tried re-writing the data on another sheet (and got the same results). Also, this issue occurs when I order the Topic Values from A to Z. When ordered from Z to A, the MS Office issue seem to disappear, but I'm afraid that it can screw other things this way (300 topic items and 5000 tickets, so it's a bit hard to check it manually).
Later Edit: meanwhile I checked a few other Topics and found out that the problem might be that the formula is not looking for exact values. For example, I think it shows "MS Office" for the fields that contain "Password" because "Word" is one of the keywords for "MS Office". And "Word" is included in "Password". So even if Password is a better match, for some reason it will pick Word, hence display MS Office instead of Account.
Please correct me if I am wrong. And if not, any ideas how to fix it? OR, is there a better formula to use for this?
Thanks a lot for your time, I would really appreciate any help!