Return Category based on Keywords in a cell from array

**nazsha07** · 09-04-2012, 04:18 AM

Hi Experts,

I have been having issue of resolving the above mentioned.

Attached is the sample file.

1. I was figuring on how to search keywords on the Resolution column and then return a category matching the keywords based on the array created in "Keywords" tab.

For eg, If the keyword reset is found in the Resolution Column, the formula would search in the Keywords column and return the categories tag to Reset which is Password Reset.

2. I am not sure if the formula can be expanded to work around this.

=IF(SUM(COUNTIF(O2,{"*adapt*","*batt*","*cable*","*cartridge*","*LEGACY LAN*","*network*","*Patching*","*switch*","*dock*","*Print*","*domain*","*drum*","*fan*","*faulty*","*Bitlocker*","*TPM*","*recovery code*","*Remediat*","*mediat*","*recovery*","*jam*","*MB*","*mother*","*NCS*","*NIC*","*charge*","*refix*","*power unit*","*port*","*button*","*spacebar*","*fix*","*Commserve*","*noise*","*hard*","*flicker*","*replace*","*fuser*","*mark*","*dirty*","*smudge*","*kit*","*maintenance kit*","*toner*","*Paper Jam*","*vendor*","*Xerox*","*screen replace*"}))>0,"Break-Fix")))

Thank you all in advance for your help.

Best regards,
Shad

**dilipandey** · 09-04-2012, 04:29 AM

Hi Shad,

I have done this other way round.. resolution description is now appearing in keywords sheet :-

{=INDEX(Resolution!$A$1:$A$31,MIN(IF(ISNUMBER(SEARCH($A2,Resolution!$A$1:$A$31)),ROW(Resolution!$A$1:$A$31),"")),1)}

See attached :- Keywords against Resolution.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**nazsha07** · 09-04-2012, 10:44 PM

Hi Dilip,

Appreciate your prompt reply.Thank you.

However, it is not what is required.

1. The formula needs to capture certain keywords from the Resolution Column in the Resolution tab. The keywords would take a reference from the list of keywords in Column A of the Keywords tab.

The result would return the corresponding value in Column B of the Keywords Tab.

I hope I am not confusing you.Heheheh

Best regards,
Muhamad Irshad

**benishiryo** · 09-04-2012, 11:33 PM

hi Irshad, i found this similar thread awhile ago & thought it was interesting. but this idea isn't from me. modified it slightly to suit better your needs. see if it helps.

**nazsha07** · 09-05-2012, 12:25 AM

Originally Posted by benishiryo

hi Irshad, i found this similar thread awhile ago & thought it was interesting. but this idea isn't from me. modified it slightly to suit better your needs. see if it helps.

Hi Beni!

I tried it...WORKED LIKE A CHARM!...woo hooo!!!
Just what I was looking for...Thanks a million!!
Great Great...U wont know how relieve I am....I will then work on it more then with additional keywords as the list gets longer....

YESSSSSSSSSSSSAAAAAA!!!

THanks to Dilip for the assistance..Great help guys!

Appreciate it!