CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
sandeep
sandeep

Reputation: 31

Keyword text matching in Excel for sub category creation using functions

In excel,we are mathcing a keyword in a subject line of mail and creating the Sub category of error type.

Now the keywords list is increasing day by day. so am trying to create a formula to work on a array structure. 

**Sub_Category**                                    **Mathcing_Keywords**
Switch/ Port utilization                        threshold of high utilization 90 exceeded
Loan Related / HMDA Reports / Other Reports     loan restore
VDI Issues                                      VDI
Alerts For Zenoss                               Process not running


**Subject_Line**                                            **Created_Sub_Category_using function**
Subject: Please install SQL Client VDI Assigned to me   VDI Issues
Subject: RE: Facing issue with VDI machine              VDI Issues

Formula current we are using to create sub-category.

=IFERROR(IF(SEARCH($B$2,D2),$A$2,1),IFERROR(IF(SEARCH($B$3,D2),$A$3,1),IFERROR(IF(SEARCH($B$4,D2),$A$4,1),IFERROR(IF(SEARCH($B$5,D2),$A$5,1),"NA"))))

Any hint or help on function which is to be used to create array to get work done.

Upvotes: 0

Views: 229

Answers (1)

Mrig
Mrig

Reputation: 11702

Assuming your keywords are in different sheet called Keywords as follows:

enter image description here

Use the following formula to get the corresponding keywords from subject:

=INDEX(Keywords!$A$2:$A$5,MAX(IF(ISERROR(FIND(Keywords!$B$2:$B$5,A2)),-1,1)*ROW(Keywords!$B$2:$B$5)-1))

This is an array formula so commit it by pressing Ctrl+Shift+Enter

Result will be as follows:

enter image description here

Note: FIND is case-sensitive so if you want match to be case-insensitive use SEARCH function instead.

Upvotes: 1

Related Questions