LOOKUP + SEARCH for a Trend Report

[IUSECAPSLOCK]

Hello Everybody,

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).
Thanks a lot for your time, I would really appreciate any help!

 

[IUSECAPSLOCK]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Sorry, to add, it is not triggered only by Password (as visible in the printscreen), but also by things that are not even part of the namerange

<tbody data-remaining-comments-count="0" data-canpost="true" data-cansee="false" data-comments-unavailable="false" data-addlink-disabled="false" style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

</body>

Sorry, to add, it is not triggered only by Password (as visible in the printscreen), but also by keywords that are not even part of the namerange

<tbody data-remaining-comments-count="0" data-canpost="true" data-cansee="false" data-comments-unavailable="false" data-addlink-disabled="false" style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
</tbody>

 

[Aladin Akyurek]

Pictures or links to pictures are not that helpful...

Does the following modification solve the issue?

=LOOKUP(9.99999999999999E+307,SEARCH(" "&keywords&" "," "&AL2&" "),keywordreturn)

If it does not, care to post what you have in AL2?
<strike></strike>

 

[IUSECAPSLOCK]

Hello, Thank you for responding.

That modification would alter the formula. Excel will display an error message("You introduced too few arguments", or something like that), so it's not even a valid formula anymore.

AL2 is exactly what you see under Summary (in green) It will search in that Column for the "keywords" and return the "keywordreturn" (or "outlookkeyreturn", as it appears in the picture) according to it's findings. The result is under Topic (in green).

Now, 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?

 

[Aladin Akyurek]

if this

=LOOKUP(9.99999999999999E+307,SEARCH(keywords,AL2),keywordreturn)

does not produce any error message, neither would the suggested formula:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&keywords&" "," "&AL2&" "),keywordreturn)

 

[IUSECAPSLOCK]

Thank you, you are correct. "&" makes it look for exact values. The problem now is that it won't find the Topics for when the Summary contains a special character.
Example: "Account-Password Reset" -> It won't recognize either "account" or "password". Should I use "SUBSTITUTE" to change the "-" to a blank space? (I need to add a new column for that, is there any way to do it in the same column?)

Another weird thing: After applying the formula I was checking the N/A error results and found out that for some cells, it would only detect the keyword if I add a blank space before it. Ex: It won't detect "Password reset", but only " Password Reset". I checked the namerange and none of the keywords have blank spaces.
What can be the cause then?

 

[Aladin Akyurek]

Thank you, you are correct. "&" makes it look for exact values. The problem now is that it won't find the Topics for when the Summary contains a special character.
Example: "Account-Password Reset" -> It won't recognize either "account" or "password". Should I use "SUBSTITUTE" to change the "-" to a blank space? (I need to add a new column for that, is there any way to do it in the same column?)

Another weird thing: After applying the formula I was checking the N/A error results and found out that for some cells, it would only detect the keyword if I add a blank space before it. Ex: It won't detect "Password reset", but only " Password Reset". I checked the namerange and none of the keywords have blank spaces.
What can be the cause then?

I think we will need a data sample as this problem evolves as it does... That said:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&keywords&" "," "&SUBSTITUTE(AL2,"-"," ")&" "),keywordreturn)